WsusDBMaintenance<\/a><\/p>\n3. Install all required tools on your windows host (details see in WsusDBMaintenance.bat).<\/p>\n
4. Add the WsusDBMaintenance.bat to the task scheduler and execute the script every month.<\/p>\n
The maintenance script will do the following:<\/strong>
— Rebuild or reorganize indexes based on their fragmentation levels
— Select indexes that need to be defragmented based on the following
\u00a0 \u00a0* Page density is low
\u00a0 \u00a0* External fragmentation is high in relation to index size
— Update all statistics<\/p>\nDelete obsolete updates from the database<\/h3>\n
There is a script Microsoft often provides during premium support calls to cleanup this update metadata, however there are a few issues:<\/p>\n
\n- The query can take a *really* long time to run if there are a lot of updates to cleanup. In some cases it can take *days*<\/li>\n
- You need to stop all the WSUS services while it runs<\/li>\n
- If it fails for whatever reason, it will have to start all over because it doesn\u2019t commit the changes until it completes successfully<\/li>\n
- While it runs, the TEMPDB and Transaction logs will grow quite significantly until the data is committed<\/li>\n
- It gives no useful information on progress<\/li>\n<\/ul>\n
To find out just how many updates are waiting to be cleaned up, run this stored procedure:<\/p>\n
EXEC spGetObsoleteUpdatesToCleanup<\/p>\n
Firstly, when the script runs on a default WSUS install it can take over a minute to process *each* record. If there are thousands or tens of thousands or updates to remove this is going to take a while. There is an index you can add to the WSUS table that dramatically improves this so it happens at about 1 second per record. Microsoft confirmed this index is OK, however it is not officially supported (at time of writing)<\/p>\n
\n
USE SUSDB\nCREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].[tbRevisionSupersedesUpdate]([SupersededUpdateID])\nCREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].[tbLocalizedPropertyForRevision]([LocalizedPropertyID])<\/code><\/pre>\n<\/div>\nNow to the cleanup script. Simply this script will cleanup obsolete records, provide progress feedback and also allow you to run it in small blocks. This allows you to run in short blocks without needing to stop the WSUS server and avoids generating huge transaction loads on the SQL server.<\/p>\n
To \u201ctweak\u201d the script, modify this line with the number of updates you want to do in each block. Start with 50, see how it runs in your environment and increase as needed. Ideally don\u2019t run batches that take more than 5-10 minutes to prevent those SQL transaction logs growing.<\/p>\n
\n
IF @curitem < 101<\/code><\/pre>\n<\/div>\nIf you do want to run a larger batch that may take hours, you should of course stop the WSUS services to do so. Also, don\u2019t run this script if a WSUS Sync is in progress or scheduled to start.<\/p>\n
\n
USE SUSDB\nDECLARE @var1 INT, @curitem INT, @totaltodelete INT\nDECLARE @msg nvarchar(200)\nCREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)\nEXEC spGetObsoleteUpdatesToCleanup\nSET @totaltodelete = (SELECT COUNT(*) FROM #results)\nSELECT @curitem=1\nDECLARE WC Cursor FOR SELECT Col1 FROM #results\nOPEN WC\nFETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)\nBEGIN SET @msg = cast(@curitem as varchar(5)) + '\/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))\nRAISERROR(@msg,0,1) WITH NOWAIT\nEXEC spDeleteUpdate @localUpdateID=@var1\nSET @curitem = @curitem +1\nIF @curitem < 101\n\u00a0\u00a0\u00a0\u00a0FETCH NEXT FROM WC INTO @var1\nEND\nCLOSE WC\nDEALLOCATE WC\nDROP TABLE #results<\/code><\/pre>\n<\/div>\nIf for any reason the script is interrupted, you will find SQL still has the transaction table open and won\u2019t let you run again (There is already an object named \u2018#results\u2019 in the table). To resolve this highlight and execute the last line to drop the table. If this still doesn\u2019t help, close the SQL Studio Manager session and you should be prompted with a warning about uncommitted transactions. Select Yes to commit then reopen and start the query again. If for any reason the query is not properly closed there may be locks held on the SQL database that will prevent the normal WSUS service functioning resulting in failure of service.<\/p>\n
Shrink the database<\/h3>\n
Check if you see some tables with unused space. Run the query below to get a list of tables and its sizes – you should see the table tbXml with a lot of unused space.<\/p>\n
\n
USE SUSDB\nSELECT\n t.NAME AS TableName,\n s.Name AS SchemaName,\n p.rows, SUM(a.total_pages) * 8 AS TotalSpaceKB,\n CAST(ROUND(((SUM(a.total_pages) * 8) \/ 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,\n SUM(a.used_pages) * 8 AS UsedSpaceKB,\n CAST(ROUND(((SUM(a.used_pages) * 8) \/ 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,\n (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,\n CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) \/ 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB\nFROM\n sys.tables t\nINNER JOIN\n sys.indexes i ON t.OBJECT_ID = i.object_id\nINNER JOIN\n sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id\nINNER JOIN\n sys.allocation_units a ON p.partition_id = a.container_id\nLEFT OUTER JOIN\n sys.schemas s ON t.schema_id = s.schema_id\nWHERE\n t.NAME NOT LIKE 'dt%' AND\n t.is_ms_shipped = 0 AND\n i.OBJECT_ID > 255\nGROUP BY\n t.Name,\n s.Name,\n p.Rows\nORDER BY\n TotalSpaceMB DESC,\n t.Name<\/code><\/pre>\n<\/div>\nAfter we have release some space in the database, we can go ahead and shrink our database – please be aware, this command takes a long time to run.<\/p>\n
\n
DBCC SHRINKDATABASE(SUSDB)<\/code><\/pre>\n<\/div>\nSee the progress of the shrink process by using the query below.<\/p>\n
\n
SELECT
[status], start_time,\n CONVERT(varchar,(total_elapsed_time\/(1000))\/60) + 'M ' + CONVERT(VARCHAR,(total_elapsed_time\/(1000))%60) + 'S' AS [Elapsed],\n CONVERT(varchar,(estimated_completion_time\/(1000))\/60) + 'M ' + CONVERT(VARCHAR,(estimated_completion_time\/(1000))%60) + 'S' as [ETA],\n command, [sql_handle], database_id, connection_id, blocking_session_id, percent_complete\nFROM\n sys.dm_exec_requests\nWHERE
estimated_completion_time > 1\nORDER
by total_elapsed_time desc<\/code><\/pre>\n<\/div>\nAfter the database was shrinked we would like to shrink our database files as well. A clever way to do is documentet here<\/a>.<\/p>\n\n
\n
USE SUSDB\u00a0\nDECLARE @FileName sysname = N'SQLShack';\nDECLARE @TargetSize INT = (SELECT 1 + size*8.\/1024 FROM sys.database_files WHERE name = @FileName);\nDECLARE @Factor FLOAT = .999;\n\u00a0\nWHILE @TargetSize > 0\nBEGIN\n\u00a0\u00a0\u00a0\u00a0SET @TargetSize *= @Factor;\n\u00a0\u00a0\u00a0\u00a0DBCC SHRINKFILE(@FileName, @TargetSize);\n\u00a0\u00a0\u00a0\u00a0DECLARE @msg VARCHAR(200) = CONCAT('Shrink file completed. Target Size: ',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @TargetSize, ' MB. Timestamp: ', CURRENT_TIMESTAMP);\n\u00a0\u00a0\u00a0\u00a0RAISERROR(@msg, 1, 1) WITH NOWAIT;\n\u00a0\u00a0\u00a0\u00a0WAITFOR DELAY '00:00:01';\nEND<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n
<\/p>\n\n","protected":false},"excerpt":{"rendered":"
Sometimes our WSUS server displays the error – message below while performing some queries within the WSUS console. Our SUSDB file in C:\\Windows\\WID\\Data grew up to 50 GB during the […]<\/p>\n","protected":false},"author":1,"featured_media":1473,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[159,158,157],"class_list":["post-1423","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mswin","tag-sql-maintenance","tag-windows-server-update-services","tag-wsus"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.web-workers.ch\/wp-content\/uploads\/2021\/11\/Windows-Server-Update-Services-1280x720-1.jpg?fit=1280%2C720&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8sxjX-mX","jetpack-related-posts":[],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts\/1423","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/comments?post=1423"}],"version-history":[{"count":20,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts\/1423\/revisions"}],"predecessor-version":[{"id":1504,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/posts\/1423\/revisions\/1504"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/media\/1473"}],"wp:attachment":[{"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/media?parent=1423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/categories?post=1423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.web-workers.ch\/index.php\/wp-json\/wp\/v2\/tags?post=1423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"WSUS](\"https:\/\/i0.wp.com\/www.web-workers.ch\/wp-content\/uploads\/2021\/11\/2021-11-21-11_59_44-wsus-dead.png-634%C3%97304.png?resize=300%2C132&ssl=1\")
<\/a>