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

This article was previously published under Q330088
This article has been archived. It is offered "as is" and will no longer be updated.
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.

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
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
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 masterGOSELECT dbid, name FROM sysdatabases ORDER BY dbidGO				
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.
For more information, visit the following MSDN Web site:
perfmon performance monitor sysmon system databases counters

Article ID: 330088 - Last Review: 01/17/2015 06:06:54 - Revision: 6.4

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbsysadmin kbcode kbbug kbpending KB330088