So, you might be stuck with SUSDB maintenace issues – properly the maintence jobs won’t finish without getting timeouts? Something like this maybe?
Msg 1205, Level 13, State 54, Procedure spUpdateChangeTrackingNumber, Line 11
Transaction (Process ID 110) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
here is a script that will help you – you might have to run it multiple times
- Execute the next Query over then SUSDB database: exec spGetObsoleteUpdatesToCleanup
- Write down the number of Rows given by the output.
- You can find the SQL script that executes the same StoredProcedures as the WSUS GUI, but directly over the database. We just need to change the parameter in SELECT TOP (XXXX) for the number of rows detected on the previous step, or higher. Script download (the script can also be found at http://www.thomasmarcussen.com in the archive folder SUSDBClean.zip) (Note: The process should be quite faster than the regular CleanUp on the GUI, but is possible that it can enter a DeadLock condition due to other operation from the WSUS Server. In this case, just re-run the Script)
- Once the Script finished successfully, try again the CleanUp from the WSUS GUI. Now it should finish very fast.
- For last, in order to keep the SUSDB healthy it is recommended to run the Maintenance script again in order to leave the database reindexed.