Cleanup WSUS Synchronization Events
Cleanup Windows Server Update Services Synchronization History with this script.
Windows Server Update Services provides a great way to cleanup things like old computers, unneeded updates, and update files from the Windows Server Update Services file system and database.
This is not the sink you’re looking for.
Since Windows Server Update Services synchronizes the list of Windows Updates from the source Microsoft Servers on a regular basis, the history of sync events can become quite tedious to load in the User Interface. Frustratingly, there is no way through the user interface to remove old history for synchronization events. The SQL Server T-SQL code below creates a stored procedure that can be used to cleanup old events prior to a particular cut-off date. I run the code via a SQL Server Agent job daily, with a cut-off date of 30 days ago.
Here’s the code to create the stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
/* Removes synchronization-related events from the Windows Server Update Services history table. Rows will be purged from the table where the TimeAtServer column is prior to the @cutoff parameter. 2023-04-13 Hannah Vernon */ CREATE OR ALTER PROCEDURE dbo.PurgeSynchronizationEvents ( @cutoff datetime ) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRANSACTION; DECLARE @rows_purged bigint = 0; DECLARE @ordinals TABLE ( [EventOrdinalNumber] bigint NOT NULL ); DELETE ei OUTPUT deleted.[EventOrdinalNumber] INTO @ordinals ([EventOrdinalNumber]) FROM dbo.tbEventMessageTemplate emt INNER JOIN dbo.tbEventInstance ei ON emt.EventID = ei.EventID AND emt.EventNamespaceID = ei.EventNamespaceID WHERE emt.EventNamespaceID = 2 AND emt.EventID IN ( 381 --A synchronization was started due to its schedule. , 382 --A synchronization was started manually. , 384 --A synchronization completed successfully. , 386 --A synchronization failed. , 387 --A synchronization was canceled. , 389 --A Subscription has been modified. ) AND ei.TimeAtServer <= @cutoff; SET @rows_purged = ( SELECT COUNT_BIG(1) FROM @ordinals o ); COMMIT TRANSACTION; PRINT CONVERT(nvarchar(20), @rows_purged, 0) + N' synchronization event rows purged from dbo.tbEventInstance'; END; GO |
And here’s an example of how to run the proc:
|
1 2 |
DECLARE @cutoff_date datetime = CONVERT(datetime, N'2023-03-01 00:00:00.000', 120); EXEC dbo.PurgeSynchronizationEvents @cutoff = @cutoff_date; |
Hopefully this code helps you reduce the sync history in your WSUS database. Let me know in the comments below if you found this useful, or if it doesn’t work for some reason.