FIX: You receive an error message when MS DTC for SQL Server 2000 reuses a server process identifier (SPID) to enlist in a distributed transaction

This article describes the following about this hotfix release:

• The issues that are fixed by the hotfix package
• The prerequisites for installing the hotfix package
• Whether you must restart the computer after you install the hotfix package
• Whether the hotfix package is replaced by any other hotfix package
• Whether you must make any registry changes
• The files that are contained in the hotfix package

When Microsoft Distributed Transaction Coordinator (MS DTC) for Microsoft SQL Server 2000 reuses a server process identifier (SPID) to enlist in a distributed transaction, you receive an error message that is similar to the following:After you receive this error message, SQL Server logs several error messages that have error code 17883 in the SQL Server error log. You may have to stop and then restart the SQL Server service.

For a list of all publicly released SQL Server 2000 post-Service Pack 3a hotfixes, click the following article number to view the article in the Microsoft Knowledge Base:

This problem occurs when the SPID is used to enlist in a distributed transaction immediately after a parallel query has finished running on the same SPID. In this case, the internal state of the SPID is damaged by the SQL Server internal deadlock monitor or by a query against the master.dbo.sysprocesses table when the parallel query is running.

For example, the following sequence of events causes this problem:

1. SQL Server creates a SPID to run a SQL query or a SQL stored procedure. The execution plan of the SQL query or of the SQL stored procedure is parallel.
2. The SQL Server internal deadlock monitor wakes up and runs a check of the state of all the SPIDs before the SQL query or the SQL stored procedure from step 1 finishes.
3. The SQL query or the SQL stored procedure from step 1 finishes, and the SPID is no longer running on multiple parallel threads. The SQL query or the SQL stored procedure marks the internal state of the SPID as single threaded. However, the SQL Server internal deadlock monitor has not finished the check of the state of all the SPIDs.
4. The SQL Server internal deadlock monitor finishes the check of the state of all the SPIDs. The SQL Server internal deadlock monitor incorrectly changes the internal state of the SPID to NULL instead of leaving the internal state of the SPID marked as single threaded.
5. MS DTC makes a request to SQL Server to enlist in a distributed transaction. This request reuses the same damaged internal SPID structures from step 4. Because the SPID is damaged, the distributed transaction enlistment appears to be running on a SPID that is not single threaded.
6. Before SQL Server finishes the enlistment in the distributed transaction, SQL Server checks to make sure that the SPID is single threaded. The check fails because the internal state of the SPID is incorrect. You receive the error message that is mentioned in the "Symptoms" section.
7. After the check fails, the request from MS DTC to SQL Server is ended. Some latches and spinlocks on internal SQL Server resources may not be released correctly.
8. Future SQL queries or SQL stored procedures may cause one or more SQL Server schedulers to stop responding. The SQL Server schedulers may wait for the release of the latches and the spinlocks on the orphaned internal SQL Server resources.
9. SQL Server may detect that one SQL Server scheduler is not responding and may log error messages that have error code 17883 in the SQL Server error log.
10. After the SQL Server scheduler stops responding, you may not be able to log in to SQL Server. The SQL queries that are currently running may also stop responding. You may have to stop and then restart the MSSQLServer service.

Note This sequence of events requires that a parallel query is followed by a distributed transaction enlistment on the same SPID. The client application that runs the parallel query most likely does not use connection pooling. This is because any call to the sp_resetconnection stored procedure from connection pooling resets the internal state of the connection. Resetting the internal state of the connection prevents this problem.

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

• Microsoft SQL Server 2000 Service Pack 4 (SP4)
  
  For information about how to obtain SQL Server 2000 SP4, click the following article number to view the article in the Microsoft Knowledge Base:
  290211  (http://support.microsoft.com/kb/290211/ ) How to obtain the latest SQL Server 2000 service pack

Restart information

You do not have to restart the computer after you apply this hotfix.

Registry information

You do not have to change the registry.

Hotfix file information

This hotfix contains only those files that are required to correct the issues that this article lists. This hotfix may not contain all the files that you must have to fully update a product to the latest build.

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

SQL Server 2000 32-bit versions

SQL Server 2000 64-bit version

Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

To work around this problem, use one of the following methods:

• Disable parallelism for the instance of SQL Server by limiting the degree of parallelism to one degree. To do this, use the following code example.

  sp_configure 'max degree of parallelism', 1
  go
  reconfigure with override
  

  Note If the server has multiple processors and high-cost queries that regularly use parallelism, disabling parallelism may have an adverse effect on performance for those queries.
• Eliminate the use of distributed transactions.
  
  Note This method may not be realistic in most environments.

You cannot use the OPTION (MAXDOP 1) hint against a single query to work around this problem. To work around this problem in this manner, you would have to use this hint against all ad hoc queries and against all stored procedures. This method may not be realistic in most environments.

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

For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the terminology that Microsoft uses when Microsoft corrects software after it is released, click the following article number to view the article in the Microsoft Knowledge Base: For more information about parallelism, see the "max degree of parallelism Option" topic in SQL Server Books Online, or visit the following Microsoft Developer Network (MSDN) Web site:For more information about distributed transactions, see the "Distributed Transactions" topic in SQL Server Books Online, or visit the following MSDN Web site: