BUG: SQLServer:Databases Performance Counters Limited to First 99 Databases in Windows System Monitor (330088)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions), when used with:
    • the operating system: Microsoft Windows 2000
  • Microsoft SQL Server 7.0, when used with:
    • the operating system: Microsoft Windows 2000
This article was previously published under Q330088
SQL Server 8.0:356429

SYMPTOMS

The Microsoft Windows 2000 System Monitor (Performance Monitor) performance object SQLServer:Databases can display and measure counter instances only for the first 99 databases of each SQL Server 7.0 or SQL Server 2000 instance.

The _Total counter instance for the SQLServer:Databases object is the sum of the counter values up to the first 99 databases, as shown in the list of counter instances, not the complete sum of all SQL Server databases.

Selecting the All instances option of any database counter selects all the databases in the instance list. The list is limited to the first 99 databases in SQL Server and includes the _Total instance sum.

WORKAROUND

To measure database counters for more than the 99 SQL Server databases, set up a named instance of SQL Server for the additional databases and monitor the Databases counters for 99 databases of each instance of SQL Server separately.


Installing multiple SQL Server named instances makes this workaround possible because when more than one instance of SQL Server is installed, each instance has its own performance object in the list of objects. The default instance is SQLServer:Databases, while a named instance is in the format of MSSQL$InstanceName:Databases.

For additional information about the counter object naming conventions, click the following article number to view the article in the Microsoft Knowledge Base:

272433 BUG: SQL Server Books Online Incorrectly States the Names of the SQL Server 2000 Counters for Named Instances

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Microsoft SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running SQL Server. Each object contains one or more counters that determine various aspects of the objects to monitor.

Generally, if a performance object type has multiple counter instances available, you can add counters to track resource statistics for a single instance, multiple instances, or all instances at the same time. Multiple instances of the SQLServer:Databases object, each representing a resource for a single database, can be monitored at the same time. There is a counter instance for each of the first 99 databases in SQL Server and only the first 99 databases can be counted by using the _Total counter instance. The databases shown as counter instances are the first 99 databases for an instance of SQL Server when the databases are sorted by the database id (DBID), including the user databases and the system databases such as master, model, msdb, and tempdb. Run the following query in SQL Query Analyzer to get the DBID for each database:
USE master
GO
SELECT dbid, name FROM sysdatabases ORDER BY dbid
GO
In addition to the performance counters, performance condition alerts are only available for the first 99 databases. Databases created after the first 99 databases are not included in the sysperfinfo system table; therefore, when you use the sp_add_alert procedure, the procedure may return an error.

REFERENCES

For more information, visit the following MSDN Web site:

http://msdn.microsoft.com/library/en-us/architec/8_ar_aa_7tpu.asp

Modification Type:MajorLast Reviewed:4/6/2004
Keywords:kbSysAdmin kbcode kbbug kbpending KB330088 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.