KB2491214 - "Non-yielding Scheduler" error and SQL Server 2008 or SQL Server 2008 R2 stops responding intermittently in Windows Server 2008 or in Windows Server 2008 R2

Symptoms

Assume that you install Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2 on a computer that is running Windows Server 2008 or Windows Server 2008 R2. When an application connects to SQL Server 2008 or SQL Server 2008 R2 by using Microsoft SQL Server Native Client 10.0, you receive a "Non-yielding Scheduler" error intermittently. Note Each "Non-yielding Scheduler" error is logged in the SQL Server 2008 or SQL Server 2008 R2 error log file.Additionally, SQL Server stops responding intermittently. To work around this issue, you might have to restart SQL Server. Notes

This hotfix applies to both the SQL Native Client and to the SQL Server enginee.
If you have installed SQL Server 2008 Service Pack 2 (SP2), the "Non-yielding Scheduler" error does not occur, and SQL Server 2008 does not stop responding. However, you may receive an error message that resembles the following:

error code 26073 - // ErrorFormat: TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket

Cause

This issue occurs because SQL Server Native Client 10.0 handles a TCP close call on a socket incorrectly.

Resolution

Note

If you recently applied the SQL Server GDR fixes according to security bulletin MS11-049, you cannot apply the cumulative updates that are mentioned in this article. When you try to apply the fixes by using the cumulative update setup, you receive an error message that resembles the following:

A SQL Server update with a higher version has already been installed on SQL Server instance <instance name>, so the current SQL Server update cannot be applied. The version of the SQL Server update that is already installed is GDR 10.2.4064.0 (10.2.4310.0) with a KB2494089 and the current SQL Server update is 10.2.4272.0 with a KB2467239.

In this scenario, you must apply the GDR package that contains the fix for the issue that is mentioned in this KB article in addition to the fix for the MS11-049 issue. To find the corresponding GDR and QFE packages that apply to your range of SQL Server builds, refer to the FAQ section of the following security bulletin:

http://www.microsoft.com/technet/security/bulletin/MS11-049.mspxFor example, if you are running SQL Server 2008 Service Pack 2, and you applied the GDR hotfix for KB2494089, to obtain the fix for KB2491214, you must apply the QFE KB2494094 update.

Cumulative update information

To resolve this issue, apply the appropriate updates for SQL Server and for Windows Server that are running on the computer. Note You may experience this issue in a client application which uses SQL Server Native Client and a function (such as the CreateProcess function) to create a new child process. In this case, you have to apply both hotfixes for Windows Server and SQL Server.

Updates for SQL Server

Cumulative update package 6 for SQL Server 2008 R2

The fix for this issue was first released in Cumulative Update 6. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:

2489376 Cumulative Update package 6 for SQL Server 2008 R2 Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

981356 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was releasedCumulative update package 2 for SQL Server 2008 Service Pack 2 The fix for this issue was first released in Cumulative Update 2 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2467239 Cumulative update package 2 for SQL Server 2008 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2402659 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 2 hotfix to an installation of SQL Server 2008 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Updates for Windows Server

Hotfix for Windows Server 2008 R2

976932 Information about Service Pack 1 for Windows 7 and for Windows Server 2008 R2

Hotfix for Windows Server 2008

2398202 An application may stop responding in Windows Server 2008 or in Windows Vista when the application closes a socket connection or shuts down

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Workaround

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756 How to back up and restore the registry in WindowsTo work around this issue, add the SuperSocketNetlib\Tcp\TcpAbortiveClose value in the registry key for MSSQLServer. To do this, follow these steps:

Open Registry Editor. To do this, use the appropriate method for your operating system:
- In Windows Vista, click Start, type regedit in the Start Search box, and then press ENTER.
- In Windows XP, click Start, click Run, type regedit, and then click OK.
Locate and then click the following registry subkey.
- For the default instance of SQL Server 2008 or SQL Server 2008 R2, click the following registry subkey:
  
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\
- For a named instance of SQL Server 2008 or SQL Server 2008 R2, click the following registry subkey:
  
  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\Tcp\
On the Edit menu, point to New, and then click DWORD Value.
Type TcpAbortiveClose, and then press ENTER.
On the Edit menu, click Modify.
Click Decimal, and then type 1 in the Value data box.
Exit Registry Editor.
Restart SQL Server.

References

For more information about how to install SQL Server Native Client, visit the following Microsoft Developer Network (MSDN) website:

How to install SQL Server Native ClientFor more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problemsFor more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499New naming schema for Microsoft SQL Server software update packagesFor more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates