In WSUS, I've been recently experiencing a specific error...

ApplicationException: Failed to approve some updates --->System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.CatalogSyncThreadProcessReal(Boolean allowRedirect)

Specifically, this was on my WSUS replica server. Come to find out, it's a simple fix.

Basically, there is bug that can exist with some replica servers, regardless of using MSDE or SQL, wherein the deault database is missing an approval index field, which causes long delays on approving updates.

EDIT: Brett commented below that this problem can also occur on non-replica servers as well. *shrug*

So here is the fix:
1) Create a text file named addDeploymentIndex.sql

2) Add the following text to the sql file:
USE SUSDB
GO
BEGIN TRAN
IF NOT EXISTS (SELECT * FROM sysindexes where name='nc7DeploymentRevision')
BEGIN
CREATE NONCLUSTERED INDEX nc7DeploymentRevision ON dbo.tbDeployment(RevisionID, TargetGroupID, ActionID)
END
COMMIT TRAN
GO
[Note: the "create nonclusted" line probably word wrapped in this post... it will need to be one long line ending with "ActionID)"]

3) Place the file in C:\Program Files\Update Services\Tools\osql.

4) Here's where is starts getting tricky. On the WSUS server, open the command prompt and move to the directory you stored the file in. The command you need to execute will generally be this:

osql -E -S %servername%\WSUS -n -b -i addDeploymentIndex.sql

where %servername% is the name of your WSUS server. There may or may not need to be a space between -S and the name, and I'm not sure if it is case sensitive or not. The above is good for default installations of WSUS and MSDE. If you have the WSUS server database (SUSDB) on a named instance, you will need to repalce %servername% with that named instance. The named instance information can be found in the registry at HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup\SqlServerName

If you have SQL2000 (and possibly SQL2005 though it's unsupported), you should completely omit the -S and subsequent variable to get this working.

If you are using SQL2005 Express (again, not supported), you change that one area to %servername%\SQLEXPRESS.

5) The script will run, then return you to command prompt. You will not get any notification if it was successful, but it will notify you if there are errors.

(Source information for this fix: WSUS Forums and Google Groups)

If this was helpful, feel free to drop me a comment to let me know. Also, I have written several other WSUS helps in this journal, available here.






View My Stats

(Sorry to my friendslist. I had to put enough info outside the LJ cut for the Googlebots to pick up this message and therefore be helpful to others...)