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


  • Server 2016
    4 Posts | Last post October 09, 2018
    • I am getting a "Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]" error What can I change to get this to work
    • Download an earlier version at this adress: 
      https://www.microsoft.com/en-US/download/details.aspx?id=36433
      
      And don´t forget to use the quoted string:
      
      Mey windows version is 2012:
      sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i "e:\temp\WsusDBMaintenance.sql"
    • I was having a heck of a time running the script from the cmd line.  Getting the same error on a SBS2011 box.  Finally opened Management Studio as administrator, right click SUSDB, select New Query, copy and paste script, click execute.  Running now.  I will post an update once complete.
    • UPDATE: In response to my previous post.  This was on SBS2011 running on a 2008r2 server.  Script completed in 12 minutes 49 seconds when run from Management Studio.  
      Estimated number of pages in fragmented indexes: 554608
      Estimated number of pages freed: 181453
  • WSUS Srv2008 r2 script error
    5 Posts | Last post May 29, 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"
      
    • I was also getting the "Unexpected argument" error and then read at the start of the thread that copying text from the web page can include none readable characters. Sure enough, the white space around the -i parameter was not actual spaces and caused the command to fail. If you copied the sqlcmd line directly from this web page, make sure that all white space in the command line is in fact space characters.
  • 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?
      
1 - 10 of 33 Items