Try our new Certificate Revocation List Check Tool
CRLcheck.exe is a tool developed to verify digital signatures of executable files. It collects files from known paths on your client, checks their signature, and checks Certificate Revocation Lists (CRL) and OCSP download. This helps avoid delays in launching files.
Category published:  SECURITY SQL WSUS   Click on the Category button to get more articles regarding that product.

WSUS problem or unstable when WID SQL is getting to big, how to shrink

Posted by admin on 15.07.2024

Windows Update Server crashed often on Server 2016/2022 with WID Windows Internal Database. We have seen several newer WSUS Servers crahsing or hanging more often with SRV 2016 and even SRV 2019 WSUS.

The source may be this table, which is 26GB and holds all the patch information and language descriptions.

The full WID DB went to 44GB.

The sample was a date year 2019 installed WSUS in Server 2016 OS with 10 laptops and 4 Citrix Terminal Servers. Two languages DE/EN selected, OS W10/W11, SRV 2008-2016-2022.

A second case with SRV 2019 in 07/2024 with a 11GB SUSDB we had to reindex and shrink down to 9GB to get it running again. There we had the problem that we where unable to do Synchronizations (Failed/Unknown). Firewall was open (No SSL touched) all Root Certs on Server updated but the SYNC failed.


What you need for further analysis:

– Download, install, and open SQL Server Management Studio (SSMS)

– Connect to your WID instance. The server name for WID is typically2003R2:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

2012R2:

\\.\pipe\microsoft##WID\tsql\query

\\.\pipe\MICROSOFT##WID\tsql\query

Connect to WID Windows Internal Database on Server 2016-2022.


Check for large tables in SQL

SQL QUERY to see large tables

SELECT
t.NAME AS TableName,
i.name AS IndexName,
p.[Rows],
SUM(a.total_pages) AS TotalPages,
SUM(a.used_pages) AS UsedPages,
SUM(a.data_pages) AS DataPages,
(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
(SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1 GROUP BY t.NAME, i.object_id, i.index_id, i.name, p.[Rows] ORDER BY p.[Rows] DESC; -- Order by number of rows in descending order
l

So there is 26GB of patch information alone. This is the patch info and drscription in all hundreds of languages.

SELECT TOP (1000) [XmlID]

,[RootElementXml]

,[RootElementType]

,[LanguageID]

,[RevisionID]

,[RootElementXmlCompressed]

FROM [SUSDB].[dbo].[tbXml]

The single text of the RootElementXml is so large it would crash some text editors if you cut and paste just one single row.

Solving it without complex maintenance query or scripts. You may try the maintenance query further if that does not work.

- Decline Superseded Updates

- Regularly decline superseded updates to reduce the amount of metadata stored in the tbXML table

- Check the size of the full WID DB with SQL Management Studio

- Make sure you have the same size free on C: that would be 50GB+ to repair or try to reindex safely (in times of VMware this should be possible)


As a first step, try to get rid of some old stuff. Important: that will not free up the size of the WID database.

Run the WSUS Cleanup Wizard

  • Open the WSUS console
  • Go to Options
  • Click on Server Cleanup Wizard
  • Select options to delete unneeded content files, expired updates, and superseded updates (Skip first two for safe cleanup)


Make a backup of the DB before you trash it, maybe:



Try to shrink the DB:


In SQL query
USE SUSDB;

GO

DBCC SHRINKDATABASE (SUSDB);

GO

Try to reinidex the SQL WID with this SQL query

Re-index and Shrink the WID Database

This may take very long time to run

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

If you want this fix included in a maintenance task, maybe buy the products from:

AJTek-Adam-J-Marshall | Microsoft Learn

https://learn.microsoft.com/en-us/users/ajtek-adam-j-marshall/

Please see our other WSUS stability related blog posts:

WSUS Server crash Event ID 7053,12072,12052,12042,12012,13042 (Related to memory short) - www.butsch.ch

https://www.butsch.ch/post/wsus-server-crash-event-id-70531207212052120421201213042-related-to-memory-short/

WSUS: Unable to Take WSUS SQL DB offline or Dettach (SQL Locks) - www.butsch.ch

https://www.butsch.ch/post/wsus-unable-to-take-wsus-sql-db-offline-or-dettach-sql-locks/

WSUS: Windows Update Server. Most common Problems. FAQ - www.butsch.ch

https://www.butsch.ch/post/wsus-windows-update-server-most-common-problems-faq/


 Category published:  SECURITY SQL WSUS   Click on the Category button to get more articles regarding that product.