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
(91)
Add to favorites
Windows Update
10/1/2019
E-mail Twitter del.icio.us Digg Facebook
  • Nothing happens
    1 Posts | Last post March 09, 2020
    • How can I tell this script worked?
      
      I ran sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i C:\WsusDBMaintenance.sql
      
      It just exits, no errors, no messages, nothing. I don't see any increased activity on the server indicating that it is doing something, nothing in event viewer.
  • Description is wrong
    2 Posts | Last post March 05, 2020
    • Please update the Description on this script to be clearer about the syntax that is required when using Windows Internal Database. 
      
      The description as written is WRONG. If you are using Windows Internal Database, the correct syntax of the command is actually;
      
      sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i <scriptLocation>\WsusDBMaintenance.sql
    • Thank you. This command fixed my problem.
  • no connection to sql server
    3 Posts | Last post January 15, 2020
    • Hello @all,
      i have look to many post about my error messages but can't find any soultion.
      My Enviroment:
      WIndows Server 2016
      WSUS with SQL Server connectivity
      SQL - Server WSUS is running with NT Service\MSSQL$WSUS
      TCP is enabled on SQL Server Configration Manager for WSUS and global.
      WSUS it self is running too but i have problem to see new Updates because server note crashes. Thats way i want to see if this script solved my problem.
      
      So i try to use this script but wenn i start this with:
      sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –i D:\DATA\WsusDBMaintenance.sql
      
      I get error:
      Sqlcmd: Fehler: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
      Sqlcmd: Fehler: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
      Sqlcmd: Fehler: Microsoft ODBC Driver 13 for SQL Server : 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..
      
      what doing wrong?
      Thanks in advanced for help
      
    • I have solved that problem by using the following string:
      SQLCMD.exe -S <computername>\WSUS -i "D:\DATA\WsusDBMaintenance.sql" -U "sa" -P "pwd"
    • If you are using WID please use the following syntax:
      
      sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i <scriptLocation>\WsusDBMaintenance.sql
  • Fixing QUOTED_IDENTIFIER errors - add -I to script command as specified in description
    1 Posts | Last post July 15, 2019
    • Hi,
      
      Please can the 'Description' of this script be modified so that the command line under 'To use this script with the Windows Internal Database, you should run the following command' includes the -I (capital i) parameter as specified below?
      
      I ran the script using the suggested command line but got QUOTED_IDENTIFIER warnings - simply resolved by running with -I (in addition to -i specified in the suggested command).
      
      Thank you!
  • 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
1 - 10 of 39 Items