Timeout Approving Updates in WSUS/ Approving updates takes long time in WSUS
ERROR
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
DESCRIPTION
The text of the message is:
"Windows Server Update Services error -- Web Page Dialog Windows Server Update Services encountered an error.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
[Show Details] [Close]"
You see the following information in "Show Details", :
"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.DatabaseAccess.DBConnection.DrainObsoleteConnections(SqlException e)
<snip> updateId, Int32 revisionNumber, Int32 deploymentAction, Guid targetGroupId,
String adminName, DateTime deadline, Boolean isAssigned, DateTime goLiveTime,
Int32 downloadPriority, Guid deploymentGuid, Boolean translateSqlException)
at Administration.Updates.UpdateXPost.Page_Load(Object sender, EventArgs
e)"
Response from Rajiv Poonamalli [MSFT]:
We are currently in the process of addressing some of the performance issues in this area. The steps below would fix part of the performance problems you
are facing. Please try these on your server and let us know if it solves the timeout issues.
1) Save addDeploymentIndex.sql to disk on the Windows Server Update Services server
2) As administrator, run the following command.
osql -E -S <SQL instance name> -n -b -i addDeploymentIndex.sql
The osql utility can be found under the "%ProgramFiles%\Update Services\Tools\osql" folder. Provide the servername with the -S parameter. [Replace <SQL instance name with your SQL server if you are using SQL or %computername%\WSUS if you are using WMSDE]
Content of addDeploymentIndex.sql:
--------------------8<----------------------
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
--------------------8<----------------------
Note that the text starting with "CREATE NONCLUSTERED " between the
BEGIN
...
END block needs to be one single long line, it will wrap over two
lines in this post.
If the WSUS server database (SUSDB) is not on the default SQL server instance on the machine, It is probably on a named instance. Look at the following registry key to find out which SQL server/MSDE server you have to run the command at.
HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup\SqlServerName
You can replace the value of this key for the place holder in the command below.
David Hennessey (MSFT)
As for what this does, we found that the reason the approvals were taking so long was the query plan that SQL was choosing when we delete approvals in the DB (of course, the reason we are deleting approvals at all is another story, but that is something we'll try to fix in SP1) was quite inefficient because one of the pieces of data we were querying on wasn't indexed properly.
Creation of that index allows SQL to choose a much more performant query plan and in our tests reduced the deployment delete time 'significantly'.