WSUS 3.0 SP2 Database Error when Cleanup Wizard runs against Unused Updates and Update Revisions

GxyAdam
GxyAdam used Ask the Experts™
on
I just upgraded our WSUS server from WSUS 2.0 to WSUS 3.0 SP2.  After performing the upgrade I ran the Cleanup Wizard.  All boxes were checked except for "Computers not contacting the server".  It ran for several hours and finally errored out with the Database Error below.  At that point I ran the Cleanup Wizard again but only did one option at a time.  Superseded Updates, Expired Updates, and Unneeded update Files each run fine and finish in less than a minute.  However, if I try to clean Unused updates and update revisions I get the Database Error after about two minutes.  I checked the event log after the Database Error and I'm getting event ID 7042 (full details below).  The only products I have selected are Office and Windows.  My WSUS folder is 27GB and my WsusDatabase folder is 5GB.  WSUS seems to be using the Internal Database.


Error: Database Error

An error occurred when trying to perform a database operation.  This can happen if the database is very busy, if the database service is topped, or if the connection to the database is lost.  Please contact your systems administrator if the problem persists.  Click Reset Server Node to try to connect to the server again.  "Reset Server Node"
Source: Windows Server Update
Type: Warning
Event ID: 7042

The WSUS administration console was unable to connect to the WSUS Server Database.
    
Verify that SQL server is running on the WSUS Server. If the problem persists, try restarting SQL.

System.Data.SqlClient.SqlException -- Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

Source
.Net SqlClient Data Provider

Stack Trace:
   at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
   at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
   at System.Windows.Forms.Control.Invoke(Delegate method)
   at Microsoft.UpdateServices.UI.SnapIn.Wizards.ServerCleanup.ServerCleanupWizard.OnCleanupComplete(Object sender, PerformCleanupCompletedEventArgs e)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Thanks, I actually found that second link before posting but unfortunately not having any luck with those items.

I'm not able to build a second box just to host the database.  I re-indexed the internal database yesterday.  I also have another SQL database in the same box, I stopped it long enough to try the cleanup again.  No such luck.  
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
Yes, I had to use this command line to start the reindex:

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i "C:\WsusDBMaintenance.sql"

Author

Commented:
I did DB maintenance again, and also tried the clean up script that guy made.  Still no luck.  The server basically has 0 load, and i only have about 150 computers in WSUS.  Plenty of disk space and available memory.
DonNetwork Administrator

Commented:
I would then reinstall WSUS.

You may need to refer to below if you encounter problems during the uninstall/reinstall

http://blogs.technet.com/sus/archive/2008/11/05/how-to-manually-remove-all-of-wsus.aspx


In addition to the blog above for removing all of WSUS you should also  run the following command:



 On 32-bit platforms:  msiexec /x  {CEB5780F-1A70-44A9-850F-DE6C4F6AA8FB}  callerid=ocsetup.exe

 On 64-bit platforms: msiexec /x   {BDD79957-5801-4A2D-B09E-852E7FA64D01} callerid=ocsetup.exe

Commented:
Well I fixed it, but I didn't really do anything I hadn't already done.  Maybe it was the exact order of operations.

Step 1: Server Cleanup Wizard: Unneeded update files, Expired Updates, and Superseded Updates
Step 2: Run WSUS DB maintenance script
Step 3: Server Cleanup Wizard: Unneeded update files, Expired Updates, and Superseded Updates
Step 4: Server Cleanup Wizard: Unused update and Update Revisions

After about 45 minutes it said:
Unused updates deleted: 362
Unused update revisions deleted: 7822

Step 5: Server Cleanup Wizard: Unneeded update files, Expired Updates, and Superseded Updates

In total it only removed about 3GB of data.
DonNetwork Administrator

Commented:
If anything, I at minimum lead you in the right direction. Also the database script was provided in the link here  http:#29836194

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial