重建SQL Server 2005/2008丢失的Performance counter

有时候会发现需要监控SQL Server性能的时候发现Perfom中没有SQL Server的Counter,用下面的方法可以重新加载SQL Server Perfom counter. 注意这个操作完成后需要重启SQL Server servcie,所以需要找Maintaince的时间做下面的操作。

1.First you need to identify which .ini file is used for your SQL instance. The counters for the SQL database engine are included inside the file called sqlctr.ini which can be found inside the BINN folder of SQL server.

2.Open a command prompt and navigate inside the BINN folder of the SQL instance that interests you. E.g. browse to this folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.

3.Remove the current performance counters of the SQL instancxe by using the unlodctr command.

For a default instance of SQL Server run this command:

unlodctr MSSQLSERVER

For a named instance of SQL server (e.g. called namedInstance) run this command:

unlodctr MSSQL$namedInstance

4.Now you need to reload the SQL counters.

Run this command:

lodctr sqlctr.ini

If no messages are returned, this means the command succeeded.

Restart the SQL Server service (or do a failover of the SQL Group if this is a SQL failover instance).

Now the counters should have appeared again.

Your issue might be resolved with these steps. If not, there is a possibility that the sqlctr.ini file you are using is not right (maybe it somehow got corrupted). In this case, you will need to copy the sqlctr.ini file from another SQL Server that is the same version.

So in this case the steps are:

1.Copy the sqlctr.ini file from another SQL instance’s BINN folder.

2.Use Notepad to edit the sqlctr.ini file so that the driver name is correct for the original SQL instance.

e.g. for a default instance, the [info] tab should look like this:

[info]

drivername=MSSQLServer

trusted=

symbolfile=sqlctr.h

e.g. for a named instance the [info] tab should look like this:

[info]

drivername=MSSQL$namedInstance

trusted=

symbolfile=sqlctr.h

3.Remove the current performance counters of the SQL instancxe by using the unlodctr command.

For a default instance of SQL Server run this command:

unlodctr MSSQLSERVER

For a named instance of SQL server (e.g. called namedInstance) run this command:

unlodctr MSSQL$namedInstance

4.Now you need to reload the SQL counters.

Run this command:

lodctr sqlctr.ini

If no messages are returned, this means the command succeeded.

Restart the SQL Server service (or do a failover of the SQL Group if this is a SQL failover instance).

Normally the SQL counters should have appeared by now. If not, there is a possibility that the registry keys related to the SQL performance counters (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library) are incorrect, missing or have inappropriate security settings.

You should better contact Microsoft Support Services for troubleshooting a specialized issue like this.