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

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

GxyAdamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonNetwork AdministratorCommented:
0
DonNetwork AdministratorCommented:
0
GxyAdamAuthor 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.  
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

DonNetwork AdministratorCommented:
0
GxyAdamAuthor 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"
0
DonNetwork AdministratorCommented:
0
DonNetwork AdministratorCommented:
0
GxyAdamAuthor 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.
0
DonNetwork AdministratorCommented:
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

0
GxyAdamAuthor 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DonNetwork AdministratorCommented:
If anything, I at minimum lead you in the right direction. Also the database script was provided in the link here  http:#29836194
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2003

From novice to tech pro — start learning today.