Resolve WSUS Database Error Timeouts

Problem:  

WSUS GUI times out while running back-end SQL queries.  WSUS typically times out when refreshing Updates:  All Updates, Critical Updates, Security Updates, etc.

Symptoms:  

WSUS runs slow.  WSUS IIS App pool crashes and stops.  WSUS problem requires administrator to reset server node in order to function again.  Clients cannot download WSUS updates when APP pool is down.

Reason:  

Large number of updates in the catalog grows over time.  The update catalog reaches a size that increases the load on WSUS.  The server runs out of available memory for the App pool.

Background:  

Kent Agerlund does a good job describing the problem on his blog.  Agerlund also provides a solution using a SQL script.  However, Agerlund's fix does not work for all WSUS implementations.  The script only works with the full version of SQL -not the Windows Internal Database.  For example:

The specified option 'Ad Hoc Distributed Queries' is not supported by this edition of SQL Server

Solution:  

Talented programmer, SAK, updates the script to work with WSUS 2012 running a Windows Internal Database version of SQL:

  1. SQL script to identify obsolete updates on WSUS server:
    USE [SUSDB]
    GO
    DECLARE @return_value int
    EXEC @return_value = [dbo].[spGetObsoleteUpdatesToCleanup]
    GO
  2. SQL script to delete all obsolete updates on WSUS.  N.B., the script provides feedback messages, after each update ID is deleted.
    DECLARE @minimumDeadDeploymentTime DATETIME
    DECLARE @revisionDeletionTimeThreshold INT
    SELECT @revisionDeletionTimeThreshold=RevisionDeletionTimeThreshold FROM dbo.tbConfigurationC
    IF @@ERROR <> 0
    BEGIN
        RAISERROR('spGetObsoleteUpdatesToCleanup: failed to get RevisionDeletionTimeThreshold from dbo.tbConfigurationC', 16, -1)
    END
    SET @minimumDeadDeploymentTime = DATEADD(day, 0 - @revisionDeletionTimeThreshold, getutcdate())
    
    
    declare @updateid int
    declare @msg varchar(255)
    declare cur CURSOR LOCAL for
    SELECT DISTINCT u.LocalUpdateID FROM  dbo.tbUpdate u
        INNER JOIN dbo.tbRevision r ON r.LocalUpdateID = u.LocalUpdateID
        INNER JOIN dbo.tbProperty p ON p.RevisionID = r.RevisionID
    WHERE
         p.PublicationState = 1 
         AND (p.ExplicitlyDeployable = 1 OR p.UpdateType IN ('Category', 'Detectoid'))
         AND p.ReceivedFromCreatorService <= @minimumDeadDeploymentTime
         AND NOT EXISTS (SELECT * FROM dbo.tbBundleDependency bd 
                         INNER JOIN dbo.tbRevision r1 ON bd.BundledRevisionID = r1.RevisionID
                         WHERE r1.LocalUpdateID = u.LocalUpdateID)
         AND NOT EXISTS (SELECT * FROM dbo.tbPrerequisiteDependency pd
                         INNER JOIN dbo.tbRevision r2 ON pd.PrerequisiteRevisionID = r2.RevisionID
                         WHERE r2.LocalUpdateID = u.LocalUpdateID)
         AND NOT EXISTS (SELECT * FROM dbo.tbDeployment d
                         INNER JOIN dbo.tbRevision r3 ON d.RevisionID = r3.RevisionID
                         WHERE r3.LocalUpdateID = u.LocalUpdateID
                             AND d.TargetGroupTypeID = 0
                             AND d.ActionID IN (0, 1, 3))
         AND NOT EXISTS (SELECT * FROM dbo.tbDeadDeployment dd
                         INNER JOIN dbo.tbRevision r4 ON dd.RevisionID = r4.RevisionID
                         WHERE r4.LocalUpdateID = u.LocalUpdateID
                             AND dd.TargetGroupTypeID = 0
                             AND dd.ActionID IN (0, 1, 3)
                             AND dd.TimeOfDeath > @minimumDeadDeploymentTime)
    ORDER BY u.LocalUpdateID DESC
    
    
    open cur
    
    fetch next from cur into @updateid
    
    while @@FETCH_STATUS = 0 BEGIN
    
        --execute your sproc on each row
        --exec uspYourSproc @field1, @field2
     set @msg = 'Processing update: ' + cast(@updateid as varchar)
     RAISERROR( @msg,0,1) WITH NOWAIT
     
     exec spDeleteUpdate @updateid
     
    
        fetch next from cur into @updateid
    END
    
    close cur
    deallocate cur
    

3 Comments

  1. Thank You!
    I came from Kent Agerlunds Blog, this does not worked for me.
    But your Comment, you had the same Problem brought me to your Site. :-)

    ReplyDelete
  2. Hi! SBS2011 / WSUS 3.2, neither of these scripts do anything. They both complete, but report 0 lines. I have 80,000 unneeded updates I'm still trying to get rid of, thanks!

    ReplyDelete
  3. Start planning to get off SBS2011 - running a system that's only in extended support for another two years is just opening yourself to trouble.

    Try this script from the comments in the original "house of cards" article to force the WSUS cleanup to work recursively rather than trying the low-level DB scripts. It will be slower but might get you there.

    function Recursive-Cleanup(){
    try{
    Invoke-WsusServerCleanup -CleanupObsoleteUpdates
    Invoke-WsusServerCleanup -CleanupObsoleteComputers
    Invoke-WsusServerCleanup -CleanupUnneededContentFiles
    Invoke-WsusServerCleanup -DeclineExpiredUpdates
    Invoke-WsusServerCleanup -DeclineSupersededUpdates
    }
    catch [System.Data.Common.DbException]{
    $global:TimeoutCount++
    Write-Host -foreground Red “$TimeoutCount `tSQL TIMEOUT Exception. Retrying”
    Recursive-Cleanup
    }
    }
    Recursive-Cleanup

    ReplyDelete

My Instagram