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.2 Star
(79)
Add to favorites
Windows Update
6/29/2012
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • WSUS Srv2008 r2 script error
    4 Posts | Last post March 28, 2018
    • After running the command (elevated), I keep getting a response of: 
      
      "Sqlcmd: ':\\.\pipe\MSSQL##SSEE\sql\query -i C:\wsus\scripts\WsusDBMaintenance.sql': Unexpected argument. Enter '-?' for help."
      
      I do not seem to find a syntax or argument error in that referenced line. Any ideas?
    • Run the command like this: 
      
      sqlcmd -d SUSDB -i <script_location>\WsusDBMaintenance.sql
    • or this worked for me:
      
      sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query  -i C:\temp\WsusDBMaintenance.sql
      
      only getting one error when running on server 2016:
      
      
      2018-02-25 21:04:43.493 Executing: ALTER INDEX [nc_LocalUpdateID] ON [dbo].[ivwApiUpdateRevision] REBUILD WITH (FILLFACTOR = 90)
      Msg 1934, Level 16, State 1, Server MYSERVER\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.
    • the script name has to be in quotes like this:
      Sqlcmd: -S np:\\.\pipe\MSSQL##SSEE\sql\query -i "C:\wsus\scripts\WsusDBMaintenance.sql"
      
  • Incorrect Syntax near USEA/Cannot use BREAK
    2 Posts | Last post March 27, 2018
    • I am running WSUS on Windows 2016.  I have created a script that will back up my WSUS WID database, and now I'm trying to run this script, but I'm having issues.
      
      From an elevated command prompt, I'm able to execute SqlCmd.exe -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i "C:\Program Files\WSUS Maintenance\Backup.sql" Then in the same command prompt, I replaced Backup.sql with Index.sql (this script), and I receive:
      Msg102, Level15, State 1, Server Server01\MICROSOFT##WID, Line 10
      Incorrect syntax near 'USEA'
      
      I don't know where USEA is coming from, because line 10 is USE SUSDB;
      
      I'm also receiving Msg 135, Level 15, State 1, Server SERVER01\MICROSOFT##WID, Line 74
      Cannot use BREAK statement outside the scope of a WHILE statement.  It looks to me like it's inside a WHILE statement, so I don't know what is going on.
      
      I then discovered that I was supposed to include the -I switch.  I tried it, but I'm still getting the same error messages.
    • Update.  I was able to run the script via SSMS, but I DID have to remove the GO statements.  I just need to make it work via SQLCMD.
  • Any idea how long indexing can take? Just want to be prepared
    1 Posts | Last post March 14, 2018
    • I performed maintenance on the DB a few years ago and don't remember indexing it before hand. Anyone know how long I should expect it to take?  So far, running for 36 minutes...
  • Where is the download link?
    2 Posts | Last post February 25, 2018
    • Where is the download link? I do not see an option to download, can someone please help.
    • just copy the script for description and save as WsusDBMaintenance.sql
  • Server 2016 WSUS
    2 Posts | Last post January 17, 2018
    • I have the same question as @bitterswwweet and @stoutma
    • Hello,
      
      Just to tell you that the script is working well on a Windows Server 2016 build 10.0.14393.2007 with WID db using SSMS 17.4.
      
      I did this on my test VM for now. I'll try it tomorrow in production with the same setup.
      
      Thank you
      
      
  • 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)
      
1 - 10 of 32 Items