Re-index the WSUS 3.0 Database

The performance of large Windows Server Update Services (WSUS) deployments will degrade over time if the WSUS database is not maintained properly. The WSUSDBMaintenance script is

 
 
 
 
 
4.1 Star
(77)
Add to favorites
Windows Update
6/29/2012
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Server 2016 WSUS
    1 Posts | Last post November 07, 2017
    • I have the same question as @bitterswwweet
  • Server 2016 WSUS
    1 Posts | Last post October 16, 2017
    • Will this work on sql 2016 and server 2016 version of wsus?
  • Re-index the WSUS 3.0 Database for Dummy
    1 Posts | Last post October 06, 2017
    • What is the best way to run re-indexing as I am not that good with SQL or scripting.
      I see some users are dropping script in SQLSerMgr.
      What is the best approach 
      Server SBS2011
      My WSUS is on the D:\WSUS
      
      
  • Download Link???
    1 Posts | Last post July 07, 2017
    • Hey I can't even seem to find the download link.
      What happened?
      
  • ALTER INDEX failed
    3 Posts | Last post April 10, 2017
    • I'm receiving an error during the re-index but the script continues and completes the rest.
      
      Error:
      Executing: ALTER INDEX [nc_LocalUpdateID] ON [dbo].[ivwApiUpdateRevision] REBUILD WITH (FILLFACTOR = 90)
      Msg 1934, Level 16, State 1, Server ServerName\MICROSOFT##WID, Line 1
      ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
      
      We haven't done any manual or custom settings to our WSUS DB. Here is our configuration.
      
      Configuration:
      Windows 2012
      Internal WID database
      WSUS configured by SCCM
      SCCM 1610
    • Please check 'Verified on the following platforms' section of Description page, it says Windows server 2012 is not supported by this script.
    • I was able to avoid the error by adding
      
      SET QUOTED_IDENTIFIER ON;
      
      near the beginning of the script, just after SET NOCOUNT ON;.
      
      (inspired by: https://social.technet.microsoft.com/Forums/windowsserver/en-US/13181437-8902-4bc9-9933-3a53183e93dd/error-database-maintenance-on-rebuild-index?forum=winserverwsus)
      
  • what if I don't have a Windows Internal Database?
    13 Posts | Last post March 16, 2017
    • I don't think this command reflects the name of my "instance". It is Microsoft SQL Server 2008 R2 installed on the same computer as WSUS. I didn't install this stuff, though. When I run that command, I get  
      
      HResult 0x2, Level 16, State 1
      Named Pipes Provider: Could not open a connection to SQL Server [2].
      Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
      Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
      
      Can someone tell me the right command for this system or should I not use this anyway?
    • Hi,
      
      Same problem. Somebody has the solution? Thanks in advance
    • Would also like this question answered, thanks.
    • I ran into this issue when trying the command on a Small Business Server 2008 system, with WSUS using a Windows Internal Database.  I then realized that I needed to start the Command Prompt with Administrator rights.  Right-click on the Command Prompt icon and choose "Run as Administrator".  From there, I was able to run the SQLCMD script with no issues.  Hope this helps!
    • Windows Internal Database in Windows 2012 has different name. Please try 
      \\.\pipe\Microsoft##WID\tsql\query 
      instead of:
      \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    • If you have full SQL instead of WID, you are going to have to find out what network protocols are turned on, and if it is a default or named instance. 
      
      A default install of SQL would have TCP and be a default instance, so all you would need for a server name is just the name or IP address. (if running local, localhost, 127.0.0.1, or . should work) 
      
      Look at the help for sqlcmd, use it in interactive mode and see if you can connect and just do a "select @@version". Get that working to let you query the server, and then you can use the script.
      
    • I too have the same issue with Login timeout expired.
      
      Windows 2008 R2
      WSUS 3.2.7600.226
      SQL Native CLient 9.00.3042.00 (SQL 2005 - from the link link on the first page)
      SQLcmd 9.00.1399.06
      
      SQL Native client and SQLcmd are the x64 install.
      
      Have tried running using an Elevated administrators Prompt, but no dice.
      
    • Hey Guys,
      
      perhaps you have the same problem as me. Actually, you do NOT have to configure anything concerning the Named Pipes etc if you did not change anything.
      Try executing the same command in CMD (with -S not -s) - see if it works.
      
      If so, the error: HResult 0x2, Level 16, State 1 ... just tells us that the pipe you are using is not correct and it cannot reach it.
      As mentioned, for Server 2012 the pipe is '\\.\pipe\MICROSOFT##WID\tsql\query' while it is '\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query' for SBS 2011 and Windows Server 2008.
      
      Please note, that if you want to run this as a Powershell-Script, the pipe for Windows 2008/SBS 2011 contains a $. As we all know, Powershell uses $ to mark its variables...
      That is why you have to put the pipe in '' (not "").
    • For all those that do not run an internal database and instead use an MS SQL Server. This WsusDBMaintenance.sql file can be run by a DBA and probably should be if you don't know what you are doing. 
      
      Otherwise if you stumble upon it by accident and double click the file while on your database server...SSMS opens up and asks to login to the instance. You should log in with an account with Sysadmin, or DBO privs on the SUSDB database. Once in hit the execute button and after a few minutes your DB will be reindexed. Simple as that but I am a little leary about doing DBA work since I am not a DBA, the usual disclaimers apply.
    • I guess the correct command for WSUS on localhost\MSSQL instance for scheduling would be:
      sqlcmd -d SUSDB -i C:\path\WsusDBMaintenance.sql
      
    • Using SQL Server 2012/Server 2012 R2 I just double click the file to open in SQL Server Management Studio and click "Execute".  Query executed successfully.
    • Server 2012R2. I was getting the same Unexpected argument when trying to run the Sqlcmd with different switches. I ended up doing the same and just executing the query in MSE. However the only way to automate this would be cmd, since this is a hidden DB?
    • Thank you, glazewskia, for this solution:
      "
      Windows Internal Database in Windows 2012 has different name. Please try 
      \\.\pipe\Microsoft##WID\tsql\query 
      instead of:
      \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
      "
      
  • Reporting still takes forever, even after running re-index script
    4 Posts | Last post September 08, 2016
    • Script ran fine. I don't see a decrease in db or log size, either (perhaps that's not to be expected if I understand WID correctly).
      
      My SUSDB.mdf is at 1.8 GB and I'm not pulling down any updates from MS at all, just pointing my clients to the Microsoft servers to download install.
      
      I also have this script running each night to clean up WSUS entries:
      http://www.peetersonline.nl/index.php/powershell/wsus-cleanup-with-powershell/
      
      Any ideas. Reporting is kind of painful.
      
      Thanks
    • Anyone? Wondering why my WSUS database is huge and how I can shrink it. The script runs, but appears to do nothing to shrink the db.
      
      Thanks,
      JJJ
    • Hi J,
      
      this is because WSUS has two components: first all the patches, either directly downloaded from Microsoft or locally stored on the WSUS machine and secondly a database with all necessary metadata for WSUS. You are talking about the metadata database which contains not a single update but all data on how to update which version by which patch.
      
      Btw. my WSUS database is more than 4GB in size, while we locally host around 170GB of WSUS actual updates files.
      
      HTH
      
      Martin
    • sqlcmd -d SUSDB -i C:\wsus\WsusDBMaintenance.sql
      
      Is that the correct command for WSUS on localhost\MSSQL instance?
  • Time to run
    1 Posts | Last post September 06, 2016
    • Hello, this has been running for over 24H and nothing has been displayed in the CMD window. The process activity for the WSUS database is high though I am not sure if anything is happening. Anything I can check on? The log is showing nothing of interest.
  • WSUS 2012 changes
    3 Posts | Last post July 26, 2016
    • When using WSUS 2012 it requires a few changes and additions.
      
      --For new server installs, you may need to first download and install:
        1. Microsoft Command Line Utilities 11 for SQL Server:
           http://www.microsoft.com/en-us/download/details.aspx?id=36433
        2. ODBC driver 11 for SQL:
           http://www.microsoft.com/en-us/download/details.aspx?id=36434
      
      --Second, go to the SQLCMD.exe path
      cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn
      
      --Then run the following command:
      SQLCMD -E -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i <script location>
      
      As you can see, the SQL WID instance name changed in 2012, and the sql changed to tsql.  No other changes needed, the WSUS 3 maintenance script works just fine.
    • Thank you. Excellent post.
    • For Server 2012 R2 I had to use this command line:
      sqlcmd -I -S \\.\pipe\MICROSOFT##WID\tsql\query -i C:\WsusDBMaintenance.sql
  • Changes to make this work...
    4 Posts | Last post April 20, 2016
    • I run [Server 2008 R2 Standard SP1] with {WSUS 3.2.7600.251} and I added this file at the root of c: as c:\wsusDB.sql.
      
      I had to edit that sql file to add the line "SET QUOTED_IDENTIFIER ON;" and I added that line just below the line "SET NOCOUNT ON;" so it is the 4th line in the script. Now my SQL maintenance script runs to completion without errors!
      
      I use a CMD file that I right-click from my server desktop so that I can run it "AS Administrator" to invoke the SQL cleaning script while I'm logged in as Domain Administrator.
      
      The CMD file has one single line in it (including the quotes you see!)
      
      "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd.exe" -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query /i "C:\WsusDB.sql" > DBClean.txt
      
      I pipe the results to a text file so that I can study it at will.
      
      I hope my little dissertation here helps solve issues for others.
      
      -Mike Tanis
      
      
      
      
      
      
    • Thank you .. This did the trick for Server 2008 R2!
    • Thank you Mike!
      What a PITA to setup WSUS!
    • Cheers Mike Tanis - you nailed it. Thanks much!!
1 - 10 of 28 Items