Re-index the WSUS 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
(85)
Add to favorites
Windows Update
2/21/2019
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Changes to make this work...
    5 Posts | Last post March 25, 2019
    • 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!!
    • Thanks Mike, I have now got it working!
  • Compatibility 2019
    1 Posts | Last post February 22, 2019
    • hello,
      
      I just ran the script on an internal WID SUSDB with success on a Windows Server 2019, you can update the compatibilities of the script
      
      thanks
  • Incorrect Syntax near USEA/Cannot use BREAK
    4 Posts | Last post December 12, 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.
    • I had the same problem, after googling for hours I found this "SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views" at https://docs.microsoft.com/pt-br/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-2017
      
      So I added "SET QUOTED_IDENTIFIER ON;" just after "SET NOCOUNT ON;" in the beginnig of the script and now it runs with SqlCmd.
    • I am running WSUS on Windows 2012R2. Download the script at: https://gallery.technet.microsoft.com/scriptcenter/Optimize-and-cleanup-of-eb9d8640#content and start it with: sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i c:\temp\WsusDBMaintenance.sql
      It worked for me.
  • Script works beautifully on WSUS 3
    1 Posts | Last post October 28, 2018
    • Thanks for putting this together, as it helped me in my recent efforts to clean up an older WSUS 3 installation on Windows Server 2008 R2. I used it in combination with a few other bits of scripting from around the web to clear out 28,000+ obsolete updates. In the end, I came up with a bit of scripting on my own to identify and ultimately delete the obsolete update files from my installation, freeing over 1TB of disk space. For anyone interested, I've documented the whole process over on my blog:
      
      https://managingdevelopers.wordpress.com/2018/10/26/cleaning-out-old-wsus-update-files/
  • Script seems to stop and doesnt continue
    2 Posts | Last post October 26, 2018
    • Dear, 
      
      I have also run this script. and it seemed to work in the beginning but now it keeps hanging forever. 
      
      Got a 2008r2 server with Wsus version 3.x. Its a internal Database. Al services running. MMC keep crashing. I had to change the command accourding to some post here. So instead of -i I use /i. 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. 
      I use this command: 
      sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query /i C:\tmp\WsusDBMaintenance.sql
      
      And this worked in the beginning but suddenly it stopped. After copple of hours waiting on the same line I terminated the CMD. Restarted the server and tried again. Same point it keeps hanging:
      
      Changed database context to 'SUSDB'.
      Estimating fragmentation: Begin. 2016-02-18 14:10:52.887
      Number of indexes to rebuild: 52
      Estimating fragmentation: End. 2016-02-18 14:12:08.667
      2016-02-18 14:12:08.683 Executing: ALTER INDEX [PK__tbEventInstance__10B661E4] ON [dbo].[tbEventInstance] REORGANIZE
      2016-02-18 14:12:11.413 Done.
      2016-02-18 14:12:11.413 Executing: ALTER INDEX [PK__tbEventInstance__10B661E4] ON [dbo].[tbEventInstance] REORGANIZE
      2016-02-18 14:12:11.493 Done.
      2016-02-18 14:12:11.493 Executing: ALTER INDEX [nc3EventInstanceConstraint] ON [dbo].[tbEventInstance] REBUILD
      2016-02-18 14:12:11.803 Done.
      2016-02-18 14:12:11.803 Executing: ALTER INDEX [nc2EventInstance] ON [dbo].[tbEventInstance] REORGANIZE
      2016-02-18 14:12:12.600 Done.
      2016-02-18 14:12:12.600 Executing: ALTER INDEX [nc4EventInstance] ON [dbo].[tbEventInstance] REORGANIZE
      2016-02-18 14:12:13.070 Done.
      2016-02-18 14:12:13.070 Executing: ALTER INDEX [ncEventInstance_ComputerID_EventNamespaceID_EventID] ON [dbo].[tbEventInstance] REBUILD
      2016-02-18 14:12:13.257 Done.
      
      I am waiting again but it seems to do nothing anymore. Anyone encounterd this problem?
    • I know this is an old post, but I seem to be exhibiting the same behavior as you once did. Did you ever sort this out, or should I just rebuild WSUS? LOL :)
  • 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.
  • 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
      
      
1 - 10 of 35 Items