none
server cleanup wizard problem - unable to connect to the WSUS Server Database. RRS feed

  • Question

  • I'm trying to run server cleanup wizard.. it starts to run and then after a while it gives me this error:

    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 Microsoft.UpdateServices.Internal.BaseApi.SoapExceptionProcessor.DeserializeAndThrow(SoapException soapException)
       at Microsoft.UpdateServices.Internal.DatabaseAccess.AdminDataAccessProxy.ExecuteSPSearchUpdates(String updateScopeXml, String preferredCulture, ExtendedPublicationState publicationState)
       at Microsoft.UpdateServices.Internal.BaseApi.Update.SearchUpdates(UpdateScope searchScope, ExtendedPublicationState publicationState, UpdateServer updateServer)
       at Microsoft.UpdateServices.Internal.BaseApi.UpdateServer.GetUpdates(UpdateScope searchScope)
       at Microsoft.UpdateServices.UI.AdminApiAccess.UpdateManager.GetUpdates(ExtendedUpdateScope filter)
       at Microsoft.UpdateServices.UI.AdminApiAccess.WsusSynchronizationInfo.InitializeDerivedProperties()
       at Microsoft.UpdateServices.UI.AdminApiAccess.WsusSynchronizationInfo.get_NewUpdatesCount()
       at Microsoft.UpdateServices.UI.SnapIn.Pages.SyncResultsListPage.GetSyncInfoRow(WsusSynchronizationInfo syncInfo)
       at Microsoft.UpdateServices.UI.SnapIn.Pages.SyncResultsListPage.GetListRows()



    Thanks
    Wednesday, February 18, 2009 5:15 PM

Answers

  •  Some questions:

    Are there any other databases running on this Std Edition SQL service?
    [a] Yes there are, we have Kaspersky enterprise DB, Report Server DB and local application DB.

    Are there any other services running on this WSUS Server?
    [b] Yes there are, we have Active Directory, Kaspersky enterprise, SQL Server 2005, and WSUS all on the same server.

    How many days since your WSUS server was first installed?
    [c] It's been about a year now.

    What is the physical size of the SUSDB.mdf file?
    [d] 9,666,752 KB

    What is the hardware configuration of this machine, including disk drives?
    [e] Intel Xeon 1.86, 2GB Ram, HD C: 39GB - E: 25.2, running Windows Server 2003 R2 SP2.

    How many client systems are you servicing from this WSUS Server?
    [f] Around 40.

    What products/classifications are you synchronizing.

    [g] Windows XP-vista, Windows Server 2003, Office 2003-2007, SQL Server 2005.

     

    Okay, for starters, you have an underpowered/overextended machine running Active Directory, ASP.NET, and a database server, all on a sub 2GHz CPU with 2GB RAM, and not enough disk spindles. The machine has had WSUS running for about a year and is 9GB in size.

    There's no doubt in my mind that some of your performance issues are directly related to disk and database fragmentation.

    There's also no doubt that some of your performance issues are directly related to memory starvation.

    I'd suggest the following long-term fixes:

    1. Get a second machine. Make it a dedicated database server. Provision it accordingly to support servicing multiple database applications.

    2. Lacking #1, this machine needs more memory. It also needs more disk spindles. At a minimum the databases being serviced should be on a dedicated physical drive; ideally there would be two dedicated drives allocated for supporting database services. The

    For the short-term fixes, do this:

    1. During after-hours time, if you don't already have one, build a temporary machine that can act as a DC/GC, while you take this machine temporarily offline.

    1. Shutdown the Update Services service, SQL Server database engine, and any other services dependent on the SQL Server database engine (Kapersky, and other reporting applications). Disconnect from the network to temporarly eliminate DC traffic. (You could also shutdown the AD services, but disconnecting the network cable is ever-so-much easier.) Defragment ALL drives.

    2. Restart ONLY the SQL Server service. Obtain this SQL script to Reindex the WSUS Databases.

    3. Restart ONLY the Update Services service. Attempt the Server Cleanup Wizard again. Run it in two passes. Pass 1 performing everything except  remove unused updates. Pass 2 running only remove unused updates.

    4. After completion of the Server Cleanup Wizard, reconnect the machine to the network and resume all other services.

    5. If you're able to complete #3, secure the services of a well-qualified DBA to determine if there are any misconfigurations in your SQL Server setup that would account for why your WSUS database is 9GB in size -- such as improperly configured autogrowth parameters. Based on the products you're updating and only forty clients, 9GB is about 3x the maximum size I would expect to see in a WSUS database. It's possible this is simply caused by excess unused updates, it's possible it's caused by fragmentation, it's probable it was caused by unnecessary autogrowth of the database due to insufficient update maintenance. You'll want a DBA to assist you in shrinking that database after you successfully run the Database Maintenance and Server Cleanup Wizard.

     


     

     


    Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)
    Friday, February 27, 2009 2:48 PM
    Moderator

All replies

  • Can you please provide specifics of your WSUS deployment topology. Please verify the build number of the installed WSUS server (from the main page of the WSUS console), as well as the edition/service pack level of your installed SQL Server.
    Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)
    Wednesday, February 18, 2009 8:04 PM
    Moderator
  • I found out the error only happens with the unused updates and update revisions part of the server cleanup wizard.

    we have WSUS 3 with service pack 1 and Version: 3.1.6001.65

    SQL Server 2005 with Service Pack 2 and here are the version numbers from management studio:

    Microsoft SQL Server Management Studio                        9.00.3042.00
    Microsoft Analysis Services Client Tools                        2005.090.3042.00
    Microsoft Data Access Components (MDAC)                        2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
    Microsoft MSXML                        2.6 3.0 4.0 6.0
    Microsoft Internet Explorer                        7.0.5730.11
    Microsoft .NET Framework                        2.0.50727.3082
    Operating System                        5.2.3790

    Sql server and WSUS are deployed on the same server.

    I hope this will help

    Thanks
    Thursday, February 19, 2009 8:04 AM
  • Three possibilities....:

    1. You're hitting the ceiling with resource allocations running WSUS and SQL Server on the same machine, and there's not enough resource to support that function of the SCW.

    2. The SUSDB database has become heavily fragmented due to autogrowth.

    3. You simply have a large quantity of "Unused updates" and the timeouts are an accurate reflection of that status.
     
    Some questions:
    [a] Are there any other databases running on this Std Edition SQL service?
    [b] Are there any other services running on this WSUS Server?
    [c] How many days since your WSUS server was first installed?
    [d] What is the physical size of the SUSDB.mdf file?
    [e] What is the hardware configuration of this machine, including disk drives?
    [f] How many client systems are you servicing from this WSUS Server?
    [g] What products/classifications are you synchronizing.




    Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)
    Thursday, February 19, 2009 1:26 PM
    Moderator
  • Sorry for the delay, i hope you can still help us:

    [a] Yes there are, we have Kaspersky enterprise DB, Report Server DB and local application DB.
    [b] Yes there are, we have Active Directory, Kaspersky enterprise, SQL Server 2005, and WSUS all on the same server.
    [c] It's been about a year now.
    [d] 9,666,752 KB
    [e] Intel Xeon 1.86, 2GB Ram, HD C: 39GB - E: 25.2, running Windows Server 2003 R2 SP2.
    [f] Around 40.
    [g] Windows XP-vista, Windows Server 2003, Office 2003-2007, SQL Server 2005.


    Thanks
    Thursday, February 26, 2009 5:36 PM
  •  Some questions:

    Are there any other databases running on this Std Edition SQL service?
    [a] Yes there are, we have Kaspersky enterprise DB, Report Server DB and local application DB.

    Are there any other services running on this WSUS Server?
    [b] Yes there are, we have Active Directory, Kaspersky enterprise, SQL Server 2005, and WSUS all on the same server.

    How many days since your WSUS server was first installed?
    [c] It's been about a year now.

    What is the physical size of the SUSDB.mdf file?
    [d] 9,666,752 KB

    What is the hardware configuration of this machine, including disk drives?
    [e] Intel Xeon 1.86, 2GB Ram, HD C: 39GB - E: 25.2, running Windows Server 2003 R2 SP2.

    How many client systems are you servicing from this WSUS Server?
    [f] Around 40.

    What products/classifications are you synchronizing.

    [g] Windows XP-vista, Windows Server 2003, Office 2003-2007, SQL Server 2005.

     

    Okay, for starters, you have an underpowered/overextended machine running Active Directory, ASP.NET, and a database server, all on a sub 2GHz CPU with 2GB RAM, and not enough disk spindles. The machine has had WSUS running for about a year and is 9GB in size.

    There's no doubt in my mind that some of your performance issues are directly related to disk and database fragmentation.

    There's also no doubt that some of your performance issues are directly related to memory starvation.

    I'd suggest the following long-term fixes:

    1. Get a second machine. Make it a dedicated database server. Provision it accordingly to support servicing multiple database applications.

    2. Lacking #1, this machine needs more memory. It also needs more disk spindles. At a minimum the databases being serviced should be on a dedicated physical drive; ideally there would be two dedicated drives allocated for supporting database services. The

    For the short-term fixes, do this:

    1. During after-hours time, if you don't already have one, build a temporary machine that can act as a DC/GC, while you take this machine temporarily offline.

    1. Shutdown the Update Services service, SQL Server database engine, and any other services dependent on the SQL Server database engine (Kapersky, and other reporting applications). Disconnect from the network to temporarly eliminate DC traffic. (You could also shutdown the AD services, but disconnecting the network cable is ever-so-much easier.) Defragment ALL drives.

    2. Restart ONLY the SQL Server service. Obtain this SQL script to Reindex the WSUS Databases.

    3. Restart ONLY the Update Services service. Attempt the Server Cleanup Wizard again. Run it in two passes. Pass 1 performing everything except  remove unused updates. Pass 2 running only remove unused updates.

    4. After completion of the Server Cleanup Wizard, reconnect the machine to the network and resume all other services.

    5. If you're able to complete #3, secure the services of a well-qualified DBA to determine if there are any misconfigurations in your SQL Server setup that would account for why your WSUS database is 9GB in size -- such as improperly configured autogrowth parameters. Based on the products you're updating and only forty clients, 9GB is about 3x the maximum size I would expect to see in a WSUS database. It's possible this is simply caused by excess unused updates, it's possible it's caused by fragmentation, it's probable it was caused by unnecessary autogrowth of the database due to insufficient update maintenance. You'll want a DBA to assist you in shrinking that database after you successfully run the Database Maintenance and Server Cleanup Wizard.

     


     

     


    Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)
    Friday, February 27, 2009 2:48 PM
    Moderator
  • Thank you very much, I will try what you suggested and let you know what happens.
    Monday, March 2, 2009 7:40 AM
  • So did it work?
    Thursday, August 4, 2011 10:08 AM
  • So did it work?

    Almost certainly it did, but considering that the thread is almost TWO AND A HALF YEARS OLD.... the odds of getting a response to your question from the original poster is something short of nil.

    Are you having an issue with your environment (if so. please start a new thread and post the details of your issue), or are you simply trying to validatate that the post marked as an answer is the correct procedure to use to address timeout errors?


    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2011)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    My Blog: http://onsitechsolutions.spaces.live.com
    Thursday, August 4, 2011 2:17 PM
    Moderator
  • That's okay, I'll respond instead....

    I had the same problem after declining 4700 unneeded updates, though the server resources were not overextended in my case, instead it was just several years of SQL neglect.  I used the re-index script that you suggested and then ran the SCW, omitting "remove unused updates".  That alone reduced the 27.5 GB WSUS directory to 8.5 GB on a 30 GB drive. 

    Most impressive is that it did this in about a minute or so.  This usually takes hours to run, from my experience.  I'm now running the second pass as you suggested, with "remove unused updates" the only selection.  Apparently this is the one that causes the Wizard to run for so long, since there can't be that much in terms of actually data to clean up in my case.

    Thanks for these helpful suggestions, as well as for the many times you've helped me with past WSUS issues.

    Cheers.

    Thursday, April 11, 2013 4:58 PM
  • After hours or research this easy solution helped me like your situation.

    Monday, August 25, 2014 3:42 PM
  • we have WSUS 3 with service pack 1 and Version: 3.1.6001.65

    Seriously!!???

    On Windows Server 2003 I see.

    So what Service Pack level is Windows Server 2003 at? (You didn't post the full build number, and that missing fourth digit is making me nervous.)

    1. Upgrade WSUS to Service Pack 2 (released October 2009 --almost five years ago!) -- which also requires that Service Pack 2 is installed on your Windows Server 2003 system.
    2. Apply KB2734608 (released over two years ago) to the WSUS server after successfully installing WSUS Service Pack 2. Perform the required pre-installation and post-installation instructions that are documented in the KB article.
    3. Upgrade the SQL Server to Service Pack 4.

    For starters the identification of your environment suggests to me that the server has been in service at least five years, ostensibly with no maintenance performed, so the first step will be to cleanup the five years of legacy approvals that I'm sure are still present.

    Truly, you might even be better off to rebuild the WSUS installation from scratch. It's almost a certainty that you'll invest more effort in trying to clean-up/repair this server than you will building a new one from scratch.

    Sql server and WSUS are deployed on the same server.

    I'm curious why you burned a license for SQL Server, when Microsoft provides the Windows Internal Database designed exactly for this scenario.

    If you opt to rebuild the system (you should),  you can reclaim that SQL Server license and use the Windows Internal Database.


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.


    Thursday, August 28, 2014 11:37 PM
    Moderator