How To Solve SQL Server Performance Counter Missing
Summary
This Knowledge Base article provides information and workaround about SQL Server Performance Counter is missing.
More Information
Symptom
Warning message: Task
error output: [SqlServer]: Error creating counter SQLServer:Databases\Log File(s)Used Size (KB)\TMMain on 3 attempt.Exception has been trown by the target of an invocation.
Workaround
Here is how his Perfmon counters screen looks like. (Start > Run > Perfmon.exe) and then Right Click “Add Counters…”
Since we are dealing with a default instance of SQL Server, we should see “SQL Server:Access Methods” as the first counters (they are alphabetical). This got me curious and I wanted to really understand why this could ever happen. I politely asked my friend if he was ready to do some sort of screen sharing at a later date.
I looked at the SQL Server
ERRORLOG file first and there was nothing interesting under that. I
asked to query sys.dm_os_performance_counters to check if we have
counter values there. Here is the screenshot.
Asked to look into below key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance
Not many registry keys can be seen compared to the system.
The only option to fix this situation is to apply Microsoft recommended fix which is to first unload and then re-load the counters.
To unload counter
Default Instance: unlodctr MSSQLSERVER
Named Instance: unlodctr MSSQL$<InstanceName>
To load the counter we can look at the same key and look at the value of “PerfIniFile” which is “perf-MSSQLSERVERsqlctr. ini” in the above screenshot. The file is located under BINN folder, usually “%:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”
To load counter
Default Instance:
lodctr “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini”
For named instance, you need to check the file and path and run the below command (in this example the named instance is called SQL2014)
lodctr “E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\perf-MSSQL$SQL2014sqlctr.ini”
Once that is done, the counter should be available like in the screenshot below.
Applies To
Neverfail for SQL Server v201.5.12 and later
Related Articles
SQL Server Plugin Version 201.5.16 - Release Notes
Summary The SQL Server Plugin is an add-on to Neverfail Continuity Engine, which provides protection for Microsoft SQL Server. It configures Neverfail Continuity Engine to protect both the data and service items used by SQL Server application, and ...SQL Server Plugin Version 201.5.17 - Release Notes
Summary The SQL Server Plugin is an add-on to Neverfail Continuity Engine, which provides protection for Microsoft SQL Server. It configures Neverfail Continuity Engine to protect both the data and service items used by SQL Server application, and ...Neverfail for SQL Server Plug-in Version 201.5.15 - Release Notes
Summary The Neverfail for SQL Server Plug-in is an add-on to Neverfail Continuity Engine, which provides protection for Microsoft SQL Server. It configures Neverfail Continuity Engine to protect both the data and service items used by SQL Server ...Neverfail for SQL Server Plug-in Version 201.5.14 - Release Notes
Summary The Neverfail for SQL Server Plug-in is an add-on to Neverfail Continuity Engine, which provides protection for Microsoft SQL Server. It configures Neverfail Continuity Engine to protect both the data and service items used by SQL Server ...Neverfail for SQL Server Plug-in Version 201.5.13 - Release Notes
Summary This Knowledgebase article provides information about this specific release of the Neverfail for SQL Server Plug-in V201.5.13 What's new Changes since previous version: Support for SQL Server 2017 editions Added an option to include the SQL ...