This is the 5th and final part of this series on handling WSUS timeout issues, and now that we’ve removed unnecessary update approvals, addressed the issue of too many synchronized updates, and defragmented the filesystem hosting the WSUS database, we’re now ready to perform WSUS database maintenance.
In this article we’re going to talk about what the database maintenance activity does, how to get the database maintenance script, and how to successfully execute the script.
What Does WSUS Database Maintenance do?
The reference for the WSUS Database Maintenance activity is in the WSUS Operations Guide in the section titled Reindex the WSUS Database, and at its core, this is what the database maintenance script does. You could, of course, simply launch SQL Server Management Studio and reindex everything – but that would keep the database (and WSUS server) offline for a lot longer than is really necessary, potentially rebuilding indexes that don’t need to be rebuilt. The script evaluates the level of fragmentation and reindexes only those objects where the page density is low or the external fragmentation is high in relation to the index size. Specifically, the criteria are:
- Page Count > 50 and Average Fragmentation > 15%, or
- Page Count > 10 and Average Fragmentation > 80%, or
- Average Page Space Used < 85% and the Average Page Space Used is low enough that pages can be freed up by rebuilding the index
How to Get the WSUS Database Maintenance script
The script can be obtained from the Microsoft Technet Script Center. The script is not an actual downloadable object, so you will need to copy and paste the script from the webpage into your local system.
CAUTION: There is a compatibility issue between the “Copy Code” link in the script center, and certain versions of something that results in upper-bit characters being inserted into the text stream rather than the simple spaces that are actually defined. The manifestation of this issue is discussed in this thread in the TechNet WSUS forum, and I was able to reproduce it today, using the “Copy Code” link with IE9 on Win7x64 and pasting into either Notepad or SQL Server Management Studio directly. When I copied the text directly from the display on the page, I did not encounter the syntax errors. I suggest you copy the text directly, rather than use the “Copy Code” link.
How to Use the WSUS Database Maintenance script
There are two ways you can invoke this script:
- From the command line using the SQLCMD.EXE utility. If you’re using the Windows Internal Database, you’ll need to download it from the Feature Pack for Microsoft SQL Server.
- From SQL Server Management Studio. If you’re using the Windows Internal Database you will need to download and install SQL Server Management Studio Express Edition.
Either way, since you’ll need to download and install something if you’re using the WID, I suggest you go all the way and install the SQL Server Management Studio Express Edition. (You may find additional uses for this tool at another time.)
If you choose to use SQLCMD.EXE, and assuming you’ve saved the script as WSUSDBMaintenance.sql, the command to run the script from the command line is:
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –I -i <scriptLocation>\WsusDBMaintenance.sql
If you’re running this on Windows Server 2012 use:
sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query –I -i <scriptLocation>\WsusDBMaintenance.sql
To run the command from SQL Server Management Studio, open the WSUSDBMaintenance.sql file, or paste the code directly into a New Query window connected to the SUSDB and click on “Execute” or press <F5>.
Be prepared! This script will take some time to execute. On my WSUS server (P4 2.8GHz hyperthreaded, 1.5GB RAM with separate spindles for OS and WSUS), the script took almost 8 minutes to scan the WID and identify 77 indexes that qualified for maintenance. To run the entire script took almost 19 minutes.