WSUS 一直是每个 IT 管理员的祸根。您发现有多少个线程都存在同一个问题 - 服务器清理向导失败。下面的文本对我有用。
我尝试了微软和公共领域的大量 SQL 脚本，结果好坏参半。上周我们的 WSUS 服务器又开始出现故障，所以我付了钱并开了一个支持单。下面的文本与从 MS 支持收到的完全相同。就我而言，第 8 步花了一天半的时间才完成 - 即使如此也有一些错误。我忽略了这些错误。第 9 步很快，但也有几个错误。我也忽略了这些错误（死锁错误）。完成后，我重新运行了服务器清理向导，它运行完美。所有这些都是在 SQL Server Management Studio 中运行并使用 WID 的。
这不是一个问题，而是我的目标是尝试帮助其他管理员解决这些长期存在的 WSUS 问题。您的里程可能会很大 - 我希望这对你们中的一些人有所帮助。
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.