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.
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:
Removes synchronization-related events from the Windows Server Update Services
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
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @rows_purged bigint = 0;
DECLARE @ordinals TABLE
[EventOrdinalNumber] bigint NOT NULL
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 =
FROM @ordinals o
PRINT CONVERT(nvarchar(20), @rows_purged, 0) + N' synchronization event rows purged from dbo.tbEventInstance';
And here’s an example of how to run the proc:
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.
If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.