SOLVED: How To Clean Up and Repair WSUS Database

wsus-clean-up-database-scriptIf you run a Windows Server Update Services (WSUS) server, you know that sometimes it can develop problems.  Specifically the WSUS database can need a manual clean up.  There are several scripts around that handle this but I found one HERE that helped me quite a bit.

Note that this script does not replace the WSUS SERVER CLEAN UP WIZARD.  If you are having problems with that (like SERVER NODE RESET messages), click HERE for the fix.

To run this script:

  1. Download it directly from us HERE and unzip the contents
    .
  2. Start a PowerShell as an Admin
    .
  3. If you are running WSUS from a Windows Internal Database on Server 2012 enter the following command:
    .
    sqlcmd -I -S \\.\pipe\MICROSOFT##WID\tsql\query -i C:\WsusDBMaintenance.sql  
    .
    If you are running WSUS from a from a Windows Internal Database on Server 2008 or SBS 2011, enter the following command:
    .
    sqlcmd.exe -I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i C:\WSUSDBMaintenance.sql
    .updated Jul 18 2018
    (Be sure adjust the path of C:\WsusDBMaintenance.sql to reflect where you actually put the script)
    .
  4. Wait.

I have not had this take more than 15 minutes but I have read reports of it taking a VERY long time.

I found THIS post and THIS post helpful when dealing with the WSUS problem.

 

 

 

 

Comments

  1. Avatar
    Tom Greene August 12, 2019 at 7:02 am

    I have this script running for the last 28 hours and its still not finished. In task manager i see that the sql server windows nt process is using cpu and a ton of memory.
    So should i exit the script or continue.

    • Ian Matthews
      Ian Matthews August 13, 2019 at 10:06 pm

      I would let it run for a full three days as long as it was doing something with the CPU.

  2. Avatar
    MunichzFinest July 29, 2019 at 7:26 am

    Requirements to execute are:
    Microsoft® ODBC Driver 11 für SQL Server® – Windows
    Microsoft® Command Line Utilities 11 for SQL Server®

    (found this hint at https://www.hass.de/content/reindex-wsus-database-under-windows-2012)

  3. Avatar
    Tymusz Bigbusy April 14, 2019 at 10:26 pm

    whatif: I don’t have the SqlCmd? (sqlcmd : The term ‘sqlcmd’ is not recognized as the name of a cmdlet,… etc)

    • Ian Matthews
      Ian Matthews April 30, 2019 at 7:24 pm

      That likely means WSUS is not installed on that machine OR WSUS is not using the integrated database. If you are sure WSUS is on that server, check to see if its database is stored on a full SQL server.

  4. Avatar
    Peat February 13, 2019 at 6:23 am

    Thank you, sir. This saved the day and a lot of unnecessary work rebuilding.

  5. Avatar
    Rob July 11, 2018 at 9:57 am

    2nd command is wrong above. It should be:
    sqlcmd.exe -I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i C:\WSUSDBMaintenance.sql

    • Ian Matthews
      Ian Matthews July 18, 2018 at 3:50 pm

      Thanks Rob. I have corrected that typo 🙂

  6. Avatar
    Mats Webjörn June 8, 2018 at 1:10 pm

    Hi, I’m trying to run the command for a SBS2011, but PowerShell halts with a prompt “>>”, so it seems like there’s something wrong with the command

    • Ian Matthews
      Ian Matthews June 8, 2018 at 6:38 pm

      Hi Mats;

      I have seen these scripts take an hour to complete, but I have never seen them fail. Are you sure you are running POWERSHELL as an Admin (check the title bar).

  7. Avatar
    James January 31, 2017 at 2:49 pm

    C:\Tools\scripts>sqlcmd -I -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\queryÆ \query –
    i C:\WsusDBMaintenance.sql
    Sqlcmd: ‘\query’: Unexpected argument. Enter ‘-?’ for help.

Leave a Reply