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 (908156)


The information in this article applies to:

  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Developer Edition 64 bit
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (MSDE)
  • Microsoft SQL Server 2000 Workgroup Edition
Bug #: 474686 (SQL Server 8.0)
Microsoft distributes Microsoft SQL Server 2000 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2000 fix release.

SUMMARY

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

SYMPTOMS

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:
SQL Server Assertion: File: <"msqlxact.cpp">, line=1882 Failed Assertion = 'pss->IsSingleThreaded ()'.
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:

810185 SQL Server 2000 hotfix update for SQL Server 2000 Service Pack 3 and 3a

CAUSE

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.

RESOLUTION

Hotfix information

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2000 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

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 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
File nameFile versionFile sizeDateTimePlatform
Dtsui.dll2000.80.2171.01,593,34404-Nov-200507:10x86
Impprov.dll2000.80.2171.0102,40004-Nov-200507:10x86
Mssdi98.dll8.11.50523.0239,10406-Jun-200522:46x86
Ntwdblib.dll2000.80.2171.0290,81604-Nov-200507:10x86
Odsole70.dll2000.80.2171.069,63204-Nov-200507:10x86
Osql.exe2000.80.2039.057,34404-Nov-200505:13x86
Pfclnt80.dll2000.80.2171.0430,08004-Nov-200507:10x86
Procsyst.sqlNot Applicable552,06817-Jun-200500:15Not Applicable
Replprov.dll2000.80.2171.0237,56804-Nov-200507:10x86
Semexec.dll2000.80.2171.0856,06404-Nov-200507:10x86
Sp4_serv_qfe.sqlNot Applicable18,81017-Jun-200500:15Not Applicable
Spupdsvc.exe6.1.22.522,75204-Nov-200505:14x86
Sqlagent.exe2000.80.2171.0323,58404-Nov-200505:27x86
Sqldiag.exe2000.80.2171.0118,78404-Nov-200506:09x86
Sqldmo.dll2000.80.2171.04,362,24004-Nov-200507:10x86
Sqlevn70.rll2000.80.2171.045,05604-Nov-200507:10Not Applicable
Sqlfth75.dll2000.80.2171.0102,40004-Nov-200505:33x86
Sqlse.rll1.1.1003.044,54427-Sep-200505:23Not Applicable
Sqlservr.exe2000.80.2171.09,158,65604-Nov-200507:10x86
Sqlsort.dll2000.80.2171.0589,82404-Nov-200507:10x86
Sqlstpcustomdll.dll1.0.128.0943,10404-Nov-200505:13x86
Sqlstpcustomdll.rllNot Applicable24,57604-Nov-200505:13Not Applicable
Stardds.dll2000.80.2171.0176,12804-Nov-200507:10x86
Svrnetcn.dll2000.80.2171.0110,59204-Nov-200507:10x86
Tempcatsign.cdfNot Applicable1,42904-Nov-200514:57Not Applicable
Ums.dll2000.80.2171.035,32804-Nov-200507:10x86
Updatelauncher.exe5.2.3790.12888,70412-Oct-200500:07x86
SQL Server 2000 64-bit version
File nameFile versionFile sizeDateTimePlatform
Impprov.dll2000.80.2171.0244,73604-Nov-200514:16IA-64
Mssdi98.dll8.11.50523.0758,78404-Nov-200514:16IA-64
Odsole70.dll2000.80.2171.0150,52804-Nov-200514:16IA-64
Pfclnt80.dll2000.80.2171.01,187,84004-Nov-200514:16IA-64
Replprov.dll2000.80.2171.0538,62404-Nov-200514:16IA-64
Sqlagent.exe2000.80.2171.01,061,37604-Nov-200514:16IA-64
Sqldiag.exe2000.80.2171.0334,33604-Nov-200514:16IA-64
Sqldmo.dll2000.80.2171.04,362,24004-Nov-200514:16x86
Sqlevn70.rll2000.80.2171.035,32804-Nov-200514:16Not Applicable
Sqlfth75.dll2000.80.2171.0246,78404-Nov-200514:16IA-64
Sqlservr.exe2000.80.2171.024,921,60004-Nov-200514:16IA-64
Sqlsort.dll2000.80.2171.0617,47204-Nov-200514:16IA-64
Svrnetcn.dll2000.80.2171.0427,52004-Nov-200514:16IA-64
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

WORKAROUND

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.

STATUS

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

MORE INFORMATION

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:

822499 New naming schema for Microsoft SQL Server software update packages

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:

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

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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_7h9q.asp

For more information about distributed transactions, see the "Distributed Transactions" topic in SQL Server Books Online, or visit the following MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_6lo6.asp

Modification Type:MajorLast Reviewed:3/24/2006
Keywords:kbtshoot kbbug kbfix kbQFE KB908156 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.