FIX: You may notice incorrect values for the "Active Transactions" counter when you perform multiple transactions on an instance of SQL Server 2000 that is running on an SMP computer (841401)


The information in this article applies to:

  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Enterprise Evaluation Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (MSDE)
  • Microsoft SQL Server 2000 Developer Edition 64 bit
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
Bug #: 362810 (SQL Server 8.0)

SQL Server 8.0:362810
Microsoft distributes 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.

SYMPTOMS

On a symmetric multiprocessor (SMP) computer that is running Microsoft SQL Server 2000, you may notice the following behavior in the Windows Performance Monitor when you perform multiple transactions on an instance of SQL Server 2000:
  • The values for the Active Transactions counter for the _Total instance of the SQL Server:Databases object are not correct. You may also notice that the values are very large and unrealistic.
  • The values for the Active Transactions counter for the instances that correspond to individual databases are correct.

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, 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

Hotfix information

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.
   Date         Time   Version            Size    File name
   -------------------------------------------------------------------------
   23-Apr-2004  03:22  2000.80.935.0     664,128  Autoplt.dll      
   23-Apr-2004  03:22  2000.80.935.0      78,400  Console.exe      
   23-Apr-2004  03:22  2000.80.935.0     315,968  Custtask.dll     
   23-Apr-2004  03:22  2000.80.935.0      33,340  Dbmslpcn.dll     
   08-Apr-2004  09:12                    786,432  Distmdl.ldf
   08-Apr-2004  09:12                  2,359,296  Distmdl.mdf
   10-Oct-2003  06:18                        180  Drop_repl_hotfix.sql
   23-Apr-2004  03:22  2000.80.935.0   1,905,216  Dtspkg.dll       
   23-Apr-2004  03:22  2000.80.935.0     528,960  Dtspump.dll      
   23-Apr-2004  03:22  2000.80.935.0   1,557,052  Dtsui.dll        
   23-Apr-2004  03:22  2000.80.935.0     639,552  Dtswiz.dll       
   10-Oct-2003  06:18                    747,927  Instdist.sql
   10-Oct-2003  06:18                      1,581  Inst_repl_hotfix.sql
   23-Apr-2004  03:22  2000.80.935.0      82,492  Itwiz.exe        
   23-Apr-2004  03:22  2000.80.935.0      90,692  Msgprox.dll      
   23-Apr-2004  03:22  2000.80.935.0      62,024  Odsole70.dll     
   23-Apr-2004  03:23  2000.80.935.0      25,144  Opends60.dll     
   23-Apr-2004  03:22  2000.80.935.0      57,904  Osql.exe         
   23-Apr-2004  03:22  2000.80.935.0     279,104  Pfutil80.dll     
   10-Oct-2003  06:07                    550,780  Procsyst.sql
   08-Apr-2004  09:12                     12,305  Qfe469315.sql
   08-Apr-2004  09:12                     19,195  Qfe469571.sql
   22-Apr-2004  09:51                  1,104,848  Replmerg.sql
   23-Apr-2004  03:22  2000.80.935.0     221,768  Replprov.dll     
   23-Apr-2004  03:22  2000.80.935.0     307,784  Replrec.dll      
   22-Apr-2004  10:00  2000.80.935.0     159,813  Replres.rll
   10-Oct-2003  06:18                  1,087,150  Replsys.sql
   10-Oct-2003  06:18                    986,603  Repltran.sql
   23-Apr-2004  03:22  2000.80.935.0     287,304  Rinitcom.dll     
   23-Apr-2004  03:22  2000.80.935.0      66,112  Semmap.dll       
   23-Apr-2004  03:22  2000.80.935.0      57,916  Semnt.dll        
   23-Apr-2004  03:22  2000.80.935.0     492,096  Semobj.dll       
   22-Apr-2004  10:39  2000.80.935.0     172,032  Semobj.rll
   23-Apr-2004  03:22  2000.80.935.0      53,832  Snapshot.exe     
   11-Mar-2004  14:40                    117,834  Sp3_serv_uni.sql
   23-Apr-2004  03:21  2000.80.935.0      28,672  Sqlagent.dll     
   23-Apr-2004  03:22  2000.80.935.0     311,872  Sqlagent.exe     
   23-Apr-2004  03:21  2000.80.935.0     168,001  Sqlakw32.dll     
   23-Apr-2004  03:22  2000.80.935.0      33,344  Sqlctr80.dll     
   23-Apr-2004  03:22  2000.80.935.0   4,215,360  Sqldmo.dll       
   23-Apr-2004  03:22                     25,172  Sqldumper.exe    
   22-Apr-2004  09:47  2000.80.935.0      28,672  Sqlevn70.rll
   23-Apr-2004  03:22  2000.80.935.0     180,792  Sqlmap70.dll     
   23-Apr-2004  03:22  2000.80.935.0     188,992  Sqlmmc.dll       
   22-Apr-2004  10:43  2000.80.935.0     479,232  Sqlmmc.rll
   23-Apr-2004  03:22  2000.80.935.0     401,984  Sqlqry.dll       
   23-Apr-2004  03:22  2000.80.935.0      57,920  Sqlrepss.dll     
   23-Apr-2004  03:23  2000.80.935.0   7,577,681  Sqlservr.exe     
   23-Apr-2004  03:22  2000.80.935.0     590,396  Sqlsort.dll      
   23-Apr-2004  03:22  2000.80.935.0      45,644  Sqlvdi.dll       
   23-Apr-2004  03:22  2000.80.935.0     106,588  Sqsrvres.dll     
   23-Apr-2004  03:22  2000.80.935.0      33,340  Ssmslpcn.dll     
   23-Apr-2004  03:22  2000.80.935.0      82,492  Ssnetlib.dll     
   23-Apr-2004  03:23  2000.80.935.0      25,148  Ssnmpn70.dll     
   23-Apr-2004  03:23  2000.80.935.0     123,456  Stardds.dll      
   23-Apr-2004  03:23  2000.80.935.0     158,240  Svrnetcn.dll     
   23-Apr-2004  03:22  2000.80.935.0      76,416  Svrnetcn.exe     
   23-Apr-2004  03:23  2000.80.935.0      49,228  Ums.dll          
   23-Apr-2004  03:23  2000.80.935.0      98,872  Xpweb70.dll
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

WORKAROUND

If the counters that are mentioned in the "Symptoms" section are used by custom applications, you can use the following work around to resolve the problem:
  • Calculate the sum of the values for the Active Transactions counter for the instances that correspond to individual databases. This sum is the actual value of the Active Transactions counter for the _Total instance.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

MORE INFORMATION

Steps to reproduce the behavior

To reproduce the behavior, follow these steps on an SMP computer that is running SQL Server:
  1. In SQL Query Analyzer, connect to an instance of SQL Server to open a query window, and then paste the following code in the query window:
    -- Transaction for the first connection
while (1=1)
begin
begin tran
update pubs..authors set au_id=au_id
rollback
end
  2. In SQL Query Analyzer, connect to the instance of SQL Server that is mentioned in step 1, and then paste the following code in the query window:
    -- Transaction for the second connection
while (1=1)
begin
begin tran
update Northwind..Categories set Description=Description
rollback
end
  3. In SQL Query Analyzer, connect to the instance of SQL Server that is mentioned in step 1, and then paste the following code in the query window:
    -- Transaction for the third connection
while (1=1)
begin
begin tran
update pubs..authors set au_id=au_id
update Northwind..Categories set Description=Description
rollback
end
  4. Run the code that is mentioned in step 1, in step 2, and in step 3.
  5. Add the required counters in the Windows Performance Monitor to view the values. To do this, follow these steps:
    1. Click Start, and then click Run.
    2. In the Open box, type perfmon, and then click OK.
    3. Right-click in the right pane of the Performance window, and then click Add Counters.
    4. In the Add Counters dialog box, select SQL Server:Databases from the Performance object list.
    5. Select the Active Transactions counter from the list of counters.
    6. Select the _Total, the Northwind, and the Pubs instances from the list of instances, and then click Add.
    7. In the Add Counters dialog box, click Close.
In the right pane of the Performance window, you may notice incorrect values in the Average box and in the Maximum box. You may also notice that the values are much larger than the sum of the Active Transactions counters for the individual database instances.

REFERENCES

For more information about software updates, 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 SQLServer:Databases performance counters, click the following article number to view the article in the Microsoft Knowledge Base:

330088 BUG: SQLServer:Databases performance counters limited to first 99 databases in Windows System Monitor

Modification Type:MinorLast Reviewed:6/1/2006
Keywords:kbPerformanceTool kbPerfMon kbPerformance kbSQLServ2000preSP4fix kbfix kbbug kbHotfixServer kbQFE KB841401 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.