October 27, 2017 at 9:39 am
Version in use is SQL Server 2014/SP2 CU5.
I have encounted an error that others have also encountered about perfmon counters not enabled:
In the SQL ErrorLog are these errors:
Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance and ensure that the instance login account has correct registry permissions.
Error: 3409, Severity: 16, State: 1.
Windows kernel object 'Global\SQL_110_MEMOBJ_28_BIZTALK_0' already exists. It's not owned by the SQL Server service account. SQL Server performance counters are disabled.
Error: 8319, Severity: 16, State: 1.
I have looked at postings found by a Google search, but still not clear on what to do.
Some mention trying registry changes, others running 7 commands below, still others login and security changes.
Here is a couple of things that happened that may be different than the circumstances I see in the other postings:
This is a NAMED instance (called BIZTALK) that was created several months after the default instance was created.
Both instances are live. The perfmon counters for the default instance work just fine.
For the named instance, I changed the account that starts the SQL Server from the installation default to the same
ID (login) that starts the service for the default instance. I believe (but not sure) that this is when this issue appeared.
I ran these steps, but upon doing the restart I got the Log errors above:
1.Run cmd.exe as Administrator.
2.Go to the Binn directory. On my SQL 2014 instance this was
cd C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn.
3.unlodctr MSSQLSERVER (or unlodctr MSSQLSERVER$INSTANCENAME for a named instance).
4.lodctr perf-MSSQLSERVERsqlctr.ini (or lodctr perf-MSSQLSERVER$INSTANCENAMEsqlctr.ini for a named instance).
5.Restart the Remote Registry service if running.
6.Restart the Performance Logs & Alerts service if running.
7.Restart the SQL Server service.
Any help would be appreciated. Thanks in advance!
October 27, 2017 at 2:42 pm
If you think this is where it started:
I changed the account that starts the SQL Server from the installation default to the same
ID (login) that starts the service for the default instance. I believe (but not sure) that this is when this issue appeared.
I ran these steps, but upon doing the restart I got the Log errors above:
Where did you make that change for the service account? Did you use the Services applet or SQL Server Configuration Manager.
Did you ever start or restart the Remote Registry service? Try restarting (or starting) the service.
In general, If you reloaded the counters and it still didn't load them you may need to rebuild them. That is typically the order - try to reload and if that fails, rebuild.
Sue
October 30, 2017 at 5:50 am
To answer Sue_H questions: I made the change for the service account from SQL Server Config Manager - I actually do not know what the "Services applet" is. I did restart the Remote Registry service (#5 step in the sequence above that I tried before posting), and unfortunately the issue was not corrected. When you say rebuild the counters, do you mean using the lodctr \R option? If so, is a SQL Server instance bounce needed, or a total server reboot? Thanks in advance!!
October 30, 2017 at 7:24 am
I just tried the lodctr /R option (successfully run), and restarted the BizTalk SQL Server instance. No effect, still get the 8319 and 3409 errors in error log. Any help is appreciated!!
October 30, 2017 at 7:54 am
The reason for asking how the service account was changed is due to permissions only be handled correctly when using SQL Server Configuration Manager. If you go to the start button and then to run and then type in Services.msc, that will bring up the services applet and is NOT where changes should be made for SQL Server services.
If you have tried all of the steps in this article - including checking the registry:
Missing SQL Performance Counters
Then you could refer to an in depth article (which is also referenced in the above link) on troubleshooting the performance counters not loading. It's for earlier versions of SQL Server but still applicable:
Troubleshooting: SQL Server (2005, 2008) Performance Counter Collection Problems
Sue
November 2, 2017 at 9:31 am
I have tried many of the suggestions in the first link (Missing SQL Performance Counters), including the registry settings. The second link has a lot more suggestions in it, some which I have not tried yet. I did change the service account using SQL Config Manager. I am tempted to change the service account to a different ID, even though the same ID runs both instances and the perf mon counters work fine for the default instance. I will try this and the other suggestions in the second link. Thanks!
November 10, 2017 at 9:08 am
Turns out the items suggested by various posters and Google search mentioned above worked AFTER a complete server (box) reboot. Thanks!
November 10, 2017 at 9:25 am
trhorner - Friday, November 10, 2017 9:08 AMTurns out the items suggested by various posters and Google search mentioned above worked AFTER a complete server (box) reboot. Thanks!
Thanks for posting back and reporting that - glad you got it working now.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply