Missing SQL Server Performance Counters

8317

Server Error: 8317, Severity: 16, State: 1.
Server Cannot query value 'First Counter' associated with registry key 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL Server performance counters are disabled.Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033) Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

We use the “First Counter” value in the registry to identify where the counters for this SQL instance start. The “First Counter” value should map to “MSSQL$Instance:Buffer Manager” as this is the first Perfmon counter in the ini file
If it doesn’t, we throw the error mentioned above in SQL error logs.

Does your registry key look like mine?

registry

If your registry key is missing ‘First Counter’, ‘First Help’, ‘Last Counter’, ‘Last Help’ then perform the following:

Re-add extensible counters
1). Open a Command Prompt window.
2). Run:  cd %Systemroot%\System32
3). Run:  findstr drivername *.ini
4). Note the name of the .ini file for each drivername in the list.
5). At the command prompt, type the following command, and then press ENTER:
lodctr axperf.ini
— use *.ini in the each drivername list obtained in step 4
6). Repeat step 5 for each .ini file in the list.

7). Rebuild all Performance counters including extensible and third-party counters:
cd\windows\system32
lodctr /R

8). Re-load SQL performance counters then restart SQL service in a DOS command window:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> unlodctr MSSQL$MSSQLSERVER
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>lodctr perf-MSSQLSERVERsqlctr.ini
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>net stop MSSQL$MSSQLSERVER
C:\Program Files\M
icrosoft SQL Server\MSSQL.1\MSSQL\Binn>net start MSSQL$MSSQLSERVER

9). Check the SQL errorlog and confirm that there are no more errors

10). Check the registry key and confirm that ‘First Counter’, ‘First Help’, ‘Last Counter’, ‘Last Help’ key values are correct now
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$MSSQLSERVER\Performance

11). Check perfmon and confirm SQL Performance Counters are available

perfmon

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s