KB0076 - Execution Timeout Expired When Interacting with the Job Table in SQL

KB0076 - Execution Timeout Expired When Interacting with the Job Table in SQL

Scope

This article applies to PowerShell Universal environments running version 4.3.0 or earlier and configured to use SQL persistence for job history.

Problem

An error will be generated by the SQL driver when attempting to interact with the Job table within the database. The following error will be present in the log. You will also see the Groom Job health check failing. 
  1. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
  2. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  3. ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.

Root Cause

The Job table has a foreign key constraint to the JobOutput table. This table contains one record per line of job output. Due to the large table size, grooming old jobs can cause the Job and JobOutput table to lock while the delete is performed. This causes other operations to wait for access and eventually timeout. The delete itself can timeout and then will attempt to run the next time the groom job starts. This causes a perpetual issue of database locking. 

Solution

The solution is to use a nibbling delete to cut the large delete operation into many smaller deletes. This has been implemented in version 4.3.1 as part of the groom job. You can run this manually in SQL Management Studio or a similar tool. The delete will cycle through 100 jobs at a time. Due to the foreign key constraint, any associated JobOutput rows will also be deleted with this query. 
  1. -- specify the number of rows you want 
    --  to delete per 'gulp':
    DECLARE @count int
    SET @count = 100
    -- keep track of the number of rows
    --  impacted by each gulp... once it
    --  drops below the intended number of rows
    --  then you're done... 
    DECLARE @rowcount int
    SET @rowcount = @count
    -- keep date same through entire operation
    DECLARE @cutoff datetime
    SET @cutoff = GETDATE() - 30
    WHILE @count = @rowcount BEGIN
        -- archiving logic goes here... 
        -- (or it can take place BEFORE you 
        --  start with this 'nibbling operation')
        
        -- remember, nibbling deletes aren't the 
        --  only thing you can do, you can also do
        --  UPDATEs, INSERTs, MERGEs, etc. 
        DELETE FROM Job
        WHERE Id IN (
            SELECT TOP (@count) Id 
            FROM Job WITH(NOLOCK)
            WHERE
                [CreatedTime] < @cutoff
        )
        
        -- update the number of rows modified
        -- if it's less than @count, then this was
        -- our last pass... and the WHILE loop
        -- will break
        SET @rowcount = @@ROWCOUNT
                
        -- a few milliseconds is typically all you'll
        -- need for most operations, but toggle this
        -- value as needed
        WAITFOR DELAY '000:00:00.400'
    END

    • Related Articles

    • KB0077 - Startup Failure of PowerShell Universal Server in Multi-Node SQL Environment

      Scope This article applies to PowerShell Universal environments earlier than 4.3.0 that are configured with multiple PSU servers using a SQL database. Problem The PowerShell Universal server may fail to start due to SQL timeout errors. Background ...

    • KB0063 - API, App or Job Not Starting

      Problem External processes do not start after upgrades or with the same configuration and script on different machines. Common errors in the log will include: [ERR] Dashboard process running but not response after 10 seconds. Terminating process. ...

    • KB0058 - PowerShell Universal Browser Times Out Before Session Timeout When Using OIDC

      Affected Versions - All Support Versions Problem Setting the session timeout in appsettings.json does not have an affect on how soon the user is logged out from their session. Root Cause When using OpenID Connect, the -UseTokenLifetime parameter ...

    • KB - 1008 Tax/VAT Exempt at checkout

      Scope In this article you will learn how to make a tax-free purchase at checkout. Problem How do you remove the Tax/VAT on an order? Impact Orders will then need to be refunded for tax if processed without the tax deducted. Resolution Follow the ...

    • "Did not receive port from client process." when running jobs

      Version: Any Problem: When executing jobs, jobs can fail without starting the PowerShell script. Within the PowerShell Universal logs, you will see an error that states: Did not receive port from client process. Root Cause: When running in ...