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.
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
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
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:
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
, and tempdb
. Run the following query in SQL Query Analyzer to get the DBID
for each database:
SELECT dbid, name FROM sysdatabases ORDER BY dbid
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: