With that said, no matter the environment, WSUS needs regular care and feeding and I handle this need automatically with scripts others have wrote and shared. Over the years I've found many ways to do it and have settled on the following process monthly which keeps WSUS happy with little break/fix issue and even keeps the WSUS MMC snappy.
- Decline Itanium Updates
- Cleanup Wizard
- Re-Index Database
powershell.exe -ExecutionPolicy Unrestricted -nologo -NoProfile -File "PATHTOSCRIPT\WSUS_Decline_Itanium_Updates.ps1"
powershell.exe -ExecutionPolicy Unrestricted -nologo -NoProfile -File "PATHTOSCRIPT\WSUS_Cleanup_All_Servers.ps1"
You can find the WsusDBMaintenance script here, however doing a Re-Index is a bit more complex and needs to be ran on each WSUS server from the parent on down. If you use Windows Internal Database (default for WSUS) then this applies. If you use another flavor of MS SQL, then I vote to let the DBA deal with it... :)
Firstly, you have to install some prereqs as the sqlcmd called in the re-index script needs to be present. Microsoft provides it separately so you do not have to install a full edition of MS SQL to get it. Install MS SQL Server Native Client and then install the sqlcmd tool to your server. Below are links for 32-Bit and 64-Bit and there are Itanium ones out there as well. Install with defaults.
32-Bit MS SQL Server Native Client
32-Bit MS 2005 SQLCMD
64-Bit MS SQL Server Native Client
64-Bit MS 2005 SQLCMD
I wanted it to also email results to me so I grabbed blat. Great tool. Put blat and the WsusDBMaintenance script in the same folder along with the batch file below. Blat will attach the sqlcmd output as a file. Modify the setup shop variables to your environment. The scheduled task calls the batch file and runs as local administrator. Be sure to check the 'run with highest privileges' on the general tab of the scheduled task otherwise you will experience an access denied error. This is not needed on the above two powershells. I run it at 7 PM on the 2nd day of each month.
@ECHO OFF :: This script will run the WSUSDBMaintenance and email its output :: Written by Kevin Fason :: 09.12.2012 :: v1.0 :: Setup Shop SET LOGFILE=%TEMP%\WSUS_DB_reindex.txt SET EXEPATH=C:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd.exe SET SCRIPTPATH=PATHTOYOURSCRIPT SET WSUSSERVER=WSUSSERVERNAME SET SMTPTO=RECIPIENT@DOMAIN.COM SET SMTPFROM=SENDER@DOMAIN.COM SET SMTPSMARTHOST=SMTPHOST.DOMAIN.COM :: Delete previous log ECHO. ECHO Deleting previous log if it exists IF EXIST %LOGFILE% DEL %LOGFILE% :: Run WSUSDBMaintenence SQL Script ECHO. ECHO Starting WSUS DB Maintenence Script "%EXEPATH%" -I -i "%SCRIPTPATH%\wsusdbmaintenance.sql" -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query > %LOGFILE% :: Email the SQL Script output ECHO. ECHO Emailing results "%SCRIPTPATH%\blat.exe" -body "WSUS Database Maintenance Results attached. Please review. see you next time!" -to %SMTPTO% -f %SMTPFROM% -s "WSUS: DB Maintenence Results for %WSUSServer%" -server %SMTPSMARTHOST% -attach %LOGFILE%
Marks scripts do not work on more recent releases of WSUS within 2012 and 2012 R2. I found a couple great ones to use however.
First is SMSAgent wrote a PS1 that performs the same Cleanup Wizard as above and emails it to you.
Second is Jason's configmgrftw has Decline-OtherUpdates and expands ALOT on the process as a whole. His focuses on declining Itanium and other miscellaneous updates.
Additionally Microsoft has a TechNet article on it.