Re-creation of SQL Server Performance Counter

 

After some SQL Server installations, the SQL Server Performance Counter on the server is not created at all, and we notice later, or because of some operations, that Performance Counter can be delete in some way.

Just the other day, I’ve come across such a problem with another client. On a 2-node system where SQL Server 2008 Failover Cluster is installed, one of the servers had SQL Server Performance Counter. However, there were only a few Performance Counter only for SQL Server Agent and SQL Server Integration Services on the other server.

In this case, it is necessary to re-create SQL Server Performance Counter. SQL Server Performance Counters are located in the file “sqlctr.ini” in the “BINN” folder of the corresponding SQL Server Instance.

You can follow the path below to re-create the counter:

We are opening a Command Prompt window and going to the “BINN” folder of SQL Server Instance, where the Performance Counter has not been created or deleted.

For Example: “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn”.

The already existing (if any) counters are removed.

The following is the code for a default SQL Server Instance (Default Instance):

For a Named Instance, the following code must be run:

Then, the following code must be run in the “BINN” folder of the corresponding SQL Server Instance:

If no result returns after running this command, this means that the command has been executed successfully. For the settings to be enabled, the corresponding SQL Server service must be restarted. After this process, SQL Server Performance Counter should appear.

In some cases, the “sqlctr.ini” file may be corrupted in some way. If this is the case, you can copy a healthy version of this file from the “BINN” folder of another SQL Server Instance in the same version. In such a case, you must edit the following parameter in the “sqlctr.ini” file by the name of your SQL Server Instance. In the following example, SQL Server Instance is Default Instance.

Ekrem Önsoy
Author: Ekrem Önsoy

The original article was written in Turkish by Ekrem Önsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.

Leave a Reply

Your email address will not be published. Required fields are marked *