WSUS Sync Failure
“Context -
WSUS server DB were not being maintained properly.”
posted Jul 20, 2011, 4:11 PM
Today was a day of opportunity. A day of troubleshooting WSUS led to the creation of this blog with the hopes that it may assist those who want to know.
OK so down to business...got a SR assigned about WSUS sync failing. Upon investigation of this issue I checked the sync details to reveal the following message:
SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.at Microsoft.UpdateServices.DatabaseAccess.DBConnection.DrainObsoleteConnections(SqlException e) at Microsoft.UpdateServices.DatabaseAccess.DBConnection.ExecuteReader() at Microsoft.UpdateServices.Internal.DataAccess.HideUpdatesForReplicaSync(String xmlUpdateIds) at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.ProcessHiddenUpdates(Guid[] hiddenUpdates) at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.ReplicaSync() at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.ExecuteSyncProtocol(Boolean allowRedirect)
A quick Google search led me on the path to this issue being caused by a lack of maintenance. A check of the empty task scheduler library confirmed this. For those that don't know, your local WSUS server uses a Windows Internal Database (Basically a compact version of SQL) or SQL and like any database it requires regular checkups / maintenance. OK so now that I had an idea of how to fix this, my first instinct was to go to the Server Cleanup Wizard. This is located under Options of the WSUS console as shown below.
Server Cleanup Wizard is a tool that will do the maintenance we discussed earlier however it cant be scheduled out of the box (more on this later). OK so simple enough...we run the wizard and be done with this thing, right? Well I wish it was that easy. Running the tool proved to be successful except on the very first option.
Unused updates and update revisions turns out to be the mother of all of these maintenance tasks. This one will always take a long time unless scheduled regularly. If you have never ran this before I have seen it take a week. This of course is dependent upon how long your WSUS server has been operational. You may also be a victim of the following error...
So back to the web I go...searching for similar issues. I did come across several scripts that people wrote so that you can schedule all of these maintenance tasks however they would all fail with a time-out as well. Further research pointed me in the direction of performing a few SQL queries. In order to do this you need to DL and install SQL Mgmt Studio. Fortunately for us this is located on the BCK under - C:\source\SQL2008Exp\SQLManagementStudio_x64_ENU.exe. Install this and navigate to the SQL Server Configuration Manager in the start menu. Change the Protocols to enable Named Pipes as shown below and restart the DB.
Navigate to the start menu and select SQL Mgmt Studio. You should see the window below...replace local with the following:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Click connect and expand databases on the left hand pane. Input the following query as shown below:
exec spGetObsoleteUpdatesToCleanup
Click Execute
In the lower hand portion of the screen your query results should appear similar to what is shown below. Select the first record and choose Copy.
Back at the top of the menu, Click New Query and input the following:
exec spDeleteUpdate @localUpdateID=<PasteRecord>
Copy the record you captured earlier and replace <PastRecord>. Click Execute to run the query. This will actually delete this record.
If you need to do a bulk delete run the following (adjust SELECT TOP (XXX) to meet your number)
USE SUSDB GO IF object_id('tempdb..#MyTempTable') is not null DROP TABLE #MyTempTable GO IF (SELECT CURSOR_STATUS('global','myCursor')) >= -1 BEGIN DEALLOCATE myCursor END GO sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GOSELECT TOP (200) * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup')DECLARE myCursor CURSOR FOR SELECT LocalUpdateID FROM #MyTempTableDECLARE @x INT DECLARE @Msg VARCHAR(50) DECLARE @Count INT SELECT @Count = COUNT(*) FROM #MyTempTableSELECT @msg = 'Number of updates to be deleted:' + CAST( @Count AS VARCHAR(10)) RAISERROR(@msg, 0, 1) WITH NOWAITOPEN myCursor FETCH NEXT FROM myCursor INTO @xWHILE @@FETCH_STATUS = 0 BEGIN SELECT @msg = 'Deleting update with ID:' + CAST (@x AS VARCHAR(10)) RAISERROR(@msg, 0, 1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@x FETCH NEXT FROM myCursor INTO @x END CLOSE myCursor; DEALLOCATE myCursor; DROP TABLE #MyTempTable; SELECT @msg = 'Deletion completed' RAISERROR(@msg, 0, 1) WITH NOWAIT
If all goes well you should now be able to go back to the Server Cleanup Wizard and run the first option...Unused updates and update revisions. When completed...and it can take a while..we should be able to get a sync with corporate.
Last but not least....you may be asking yourself....do I have to remember to run the Server Cleanup Wizard? The answer to that is no...we have the technology....to schedule.
Someone was kind enough to create a PowerShell and VB script for us to do all of the heavy lifting and of course allow us to schedule. You can find these here:
Maintenance Tools (credentials = <domain>\credential)
You will need the SQLCMD executable for use with the CMD file. These can be extracted from a feature pack if necessary.
2005 - https://www.microsoft.com/en-us/download/details.aspx?id=15748
2012 - https://www.microsoft.com/en-us/download/details.aspx?id=29065
2014 - https://www.microsoft.com/en-us/download/details.aspx?id=42295These need to be setup as a scheduled task and coordinated with GSI. There is a particular order from bottom up that has to be followed.