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:- SQL script to identify obsolete updates on WSUS server:
USE [SUSDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spGetObsoleteUpdatesToCleanup]
GO
- 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
References:
http://blog.coretech.dk/kea/house-of-cardsthe-configmgr-software-update-point-and-wsus/http://kreelbits.blogspot.com/
3 Comments
Thank You!
ReplyDeleteI came from Kent Agerlunds Blog, this does not worked for me.
But your Comment, you had the same Problem brought me to your Site. :-)
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!
ReplyDeleteStart planning to get off SBS2011 - running a system that's only in extended support for another two years is just opening yourself to trouble.
ReplyDeleteTry 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