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.DrainObsoleteConnectio­ns(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’.

12 thoughts on “Timeout Approving Updates in WSUS/ Approving updates takes long time in WSUS”

  1. Hi,

    This worked for me. What exactly does the SQL script do? If you don’t mind explaining it to a newbie in this arena…

  2. Works very well for me:

    WSUS on W2K with MSSQL 2000 SP4. 3500 clients. Server hardware is an old test system for now – this has extended its lifespan to complete the test P)

  3. Hello,

    the script can’t find my WSUS server.

    What is the correct value for:

    HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup\SqlServerName

    You can replace the value of this key for the place holder in the command below ???

    I have: %COMPUTERNAME%\WSUS .

    Thank’s for your help.

  4. Thanks, its solved my big problem with wsus after my new installation of our windows 2003 server. Its make my life easier… approved new updates faster.

    very well, also many thanks.

  5. Success!!

    Turned my WSUS server from a turtle to a rabbit :-)) and fixed problems with some sticky updates that were recomended to deny.

  6. This did not work for me, but the problem I am having which relates to the timeout message that I get is when trying to delete a computer group from the WSUS console. I have only one WSUS server so it is not a replica server. Any ideas?

  7. Fixed all my timeout errors when changing/setting approvals on multiple updates and the slow access to the admin config pages.

    So what does this SQL script actually do??

    —–
    Main Upstream Server: (3200+ users)
    Win2003 STD SP1 / IIS 6.0

    Secondary Downstream Server: (300+ users)
    Win2000 STD SP4 / IIS 5.0

    SQL Server:
    Win2003 ENT SP1 / SQL 2000 SP4

    3500+ Workstations total
    —–

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>