Resolved: Fix WSUS Server Cleanup crashing on deleting unneeded updates

Question

WSUS has always been the bane of every IT Admin existence. How many threads do you find all with the same issue - the Server Cleanup wizard failing. The text below worked for me.

I've tried multitudes of SQL scripts both from Microsoft and in the public domain, all with mixed results. Last week our WSUS server again began coughing furballs so I paid the money and opened a support ticket. The text below is exactly as received from MS support. Step 8, in my case, took over a day and a half to complete - and even so with some errors. I ignored those errors. Step 9 was quick, but also a couple errors. I ignored those errors as well (deadlock errors). Once completed, I re-ran the Server Cleanup wizard and it ran perfect. All were run within SQL Server Management Studio and using WID.

This is not a question, but my goal to try and help other admins resolve these chronic WSUS issues. You mileage may very - I hope this helps some of you.

Please follow the below mentioned steps:

--------------------------------------------------------------------------------------------------------------------------------------------

1) Re-indexing:

USE SUSDB; 
GO 
SET NOCOUNT ON; 
 
-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int 
) 
 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @numrows int 
DECLARE @density float; 
DECLARE @fragmentation float; 
DECLARE @command nvarchar(4000);  
DECLARE @fillfactorset bit 
DECLARE @numpages int 
 
-- Select indexes that need to be defragmented based on the following 
-- * Page density is low 
-- * External fragmentation is high in relation to index size 
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
INSERT @work_to_do 
SELECT 
    f.object_id 
    , index_id 
    , avg_page_space_used_in_percent 
    , avg_fragmentation_in_percent 
    , record_count 
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
WHERE 
    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 
 
SELECT @numpages = sum(ps.used_page_count) 
FROM 
    @work_to_do AS fi 
    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
-- Declare the cursor for the list of indexes to be processed. 
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
 
-- Open the cursor. 
OPEN curIndexes 
 
-- Loop through the indexes 
WHILE (1=1) 
BEGIN 
    FETCH NEXT FROM curIndexes 
    INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    IF @@FETCH_STATUS < 0 BREAK; 
 
    SELECT  
        @objectname = QUOTENAME(o.name) 
        , @schemaname = QUOTENAME(s.name) 
    FROM  
        sys.objects AS o 
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE  
        o.object_id = @objectid; 
 
    SELECT  
        @indexname = QUOTENAME(name) 
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    FROM  
        sys.indexes 
    WHERE 
        object_id = @objectid AND index_id = @indexid; 
 
    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    ELSE 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    EXEC (@command); 
    PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
END 
 
-- Close and deallocate the cursor. 
CLOSE curIndexes; 
DEALLOCATE curIndexes; 
 
 
IF EXISTS (SELECT * FROM @work_to_do) 
BEGIN 
    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    SELECT @numpages = @numpages - sum(ps.used_page_count) 
    FROM 
        @work_to_do AS fi 
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
END 
GO 
 
 
--Update all statistics 
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO 

-------------------------------------------------------------------------------------

2) Run the below query to get the number of superseded updates.
 
SELECT UpdateID FROM vwMinimalUpdate WHERE IsSuperseded = 1 AND Declined = 0

--------------------------------------------------------------------------------------

3) Run below query to delete all the superseded updates and run server cleanup wizard.
 

DECLARE @var1 uniqueidentifier 
DECLARE @msg nvarchar(100) 
DECLARE DU Cursor
FOR
SELECT UpdateID FROM vwMinimalUpdate WHERE IsSuperseded = 1 AND Declined = 0
Open DU
FETCH NEXT FROM DU INTO @var1
WHILE (@@FETCH_STATUS > -1) 
BEGIN 
 RAISERROR(@msg,0,1) WITH NOWAIT exec spDeclineUpdate @updateID=@var1,@adminName=N'domain\user',@failIfReplica=1
FETCH NEXT FROM DU INTO @var1 
END 
CLOSE DU 
DEALLOCATE DU 

---------------------------------------------------------------------------------------

4) Run the following SQL query on SQL against SUSDB database to get number of updates to cleaned up:
 
exec spGetObsoleteUpdatesToCleanup 

----------------------------------------------------------------------------------------

5) Run the Below Query on SQL DB to Delete the updates and then run server cleanup wizard:
 
DECLARE @var1 INT 
DECLARE @msg nvarchar(100) 
 
CREATE TABLE #results (Col1 INT) 
INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup 
 
DECLARE WC Cursor 
FOR 
SELECT Col1 FROM #results 
 
OPEN WC 
FETCH NEXT FROM WC 
INTO @var1 
WHILE (@@FETCH_STATUS > -1) 
BEGIN SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1) 
RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1 
FETCH NEXT FROM WC INTO @var1 END 
CLOSE WC 
DEALLOCATE WC 
DROP TABLE #results

------------------------------------------------------------------------------------------

6) Run the below query, To know no. of updates for XML length 5000 or more:

select 
  u.UpdateID, 
  r.RevisionNumber, 
  r.RevisionID,
  lp.Title, 
  pr.ExplicitlyDeployable as ED, 
  pr.UpdateType, 
  pr.CreationDate 
 from 
  tbUpdate u 
  inner join tbRevision r on u.LocalUpdateID = r.LocalUpdateID 
  inner join tbProperty pr on pr.RevisionID = r.RevisionID 
  inner join tbLocalizedPropertyForRevision lpr on r.RevisionID = lpr.RevisionID 
  inner join tbLocalizedProperty lp on lpr.LocalizedPropertyID = lp.LocalizedPropertyID 
 where 
  lpr.LanguageID = 1033 
  and r.RevisionID in (
select
  t1.RevisionID
from
  tbBundleAll t1
  inner join tbBundleAtLeastOne t2 on t1.BundledID=t2.BundledID
where
  t2.RevisionID in(SELECT dbo.tbXml.RevisionID FROM dbo.tbXml
INNER JOIN dbo.tbProperty ON dbo.tbXml.RevisionID = dbo.tbProperty.RevisionID
where ISNULL(datalength(dbo.tbXml.RootElementXmlCompressed), 0) > 50000) and ishidden=0 and  pr.ExplicitlyDeployable=1)

--------------------------------------------------------------------------------------------------

7) If there are updates present the we decline them with the following cursor:

DECLARE @UpdateID nvarchar(100)
DECLARE @msg nvarchar(100)
 
CREATE TABLE #Updates (UpdateID nvarchar(100)) 
 
INSERT INTO #Updates(UpdateID) 
select 
  u.UpdateID
  from 
  tbUpdate u 
  inner join tbRevision r on u.LocalUpdateID = r.LocalUpdateID 
  inner join tbProperty pr on pr.RevisionID = r.RevisionID 
  inner join tbLocalizedPropertyForRevision lpr on r.RevisionID = lpr.RevisionID 
  inner join tbLocalizedProperty lp on lpr.LocalizedPropertyID = lp.LocalizedPropertyID 
 where 
  lpr.LanguageID = 1033 
  and r.RevisionID in (
select
  t1.RevisionID
from
  tbBundleAll t1
  inner join tbBundleAtLeastOne t2 on t1.BundledID=t2.BundledID
where
  t2.RevisionID in(SELECT dbo.tbXml.RevisionID FROM dbo.tbXml
INNER JOIN dbo.tbProperty ON dbo.tbXml.RevisionID = dbo.tbProperty.RevisionID
where ISNULL(datalength(dbo.tbXml.RootElementXmlCompressed), 0) > 50000) and ishidden=0 and  pr.ExplicitlyDeployable=1)
 
DECLARE UC Cursor
FOR
SELECT UpdateID FROM #Updates
 
OPEN UC
FETCH NEXT FROM UC
INTO @UpdateID
WHILE(@@FETCH_STATUS > -1)
BEGIN SET @msg = 'Declining ' + @UpdateID
RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeclineUpdate @updateID=@UpdateID,@adminName=N'mach14\administrator',@failIfReplica=1 
 
FETCH NEXT FROM UC INTO @UpdateID END
CLOSE UC

DEALLOCATE UC
DROP TABLE #Updates

----------------------------------------------------------------------------------------------

8) Declining driver updates:-
 
USE SUSDB
GO
SELECT UpdateTypeID FROM tbUpdateType WHERE Name = 'Driver'
GO
 
delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')) 
delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbInstalledUpdateSufficientForPrerequisite where prerequisiteid in (select Prerequisiteid from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')))
delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')
delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')
 
 
delete from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'


9) Delete Hidden Updates:



SELECT * FROM tbUpdate WHERE isHidden = 1
 
delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbInstalledUpdateSufficientForPrerequisite where prerequisiteid in (select Prerequisiteid from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 )))
delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbBundleAtLeastOne where bundledid in (select bundledid from tbBundleAll where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)))
delete from tbBundleAll where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbSecurityBulletinForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbKBArticleForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbRevisionSupersedesUpdate where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbBundleAtLeastOne where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbEulaProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)
delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)
delete from tbInstalledUpdateSufficientForPrerequisite where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)
delete from tbUpdate where ishidden = 1 

------------------------------------------------------------------------------------------------------------------------------

Then open WSUS console and navigate to Options\Server Cleanup Wizard.
And run the wizard one by one.

It's fine to [post your own knowledge](/help/self-answer), but it should be formatted as a question and answer, with the question above and the answer below. — Michael Hampton, Jun 28 '21 at 14:47

Resolved: Fix WSUS Server Cleanup crashing on deleting unneeded updates

0 Answers0