Missing performance counters on SQLServer 2008

New day, new problem, new solution. This time all the performance counters from SQL disapeared, showing 0 (zero) values everywhere and making monitoring the server really hard.

This strange bug happened on SQL2008-R2-x64-SP1 on Windows 2008R2-x64-SP1 , and I was able to reproduce it on Windows Server 2008-x86-SP2 with SQL2008R2-x86-SP1. A little research took me to some pretty serious troubleshooting articles:

http://blogs.msdn.com/b/psssql/archive/2009/03/03/troubleshooting-sql-server-2005-2008-performance-counter-collection-problems.aspx

http://blogs.msdn.com/b/varund/archive/2010/06/25/sql-server-missing-performance-counters-a-consolidated-list-of-known-issues.aspx

Then I remembered that the problem started after I changed the account used to start the MSSQL service from “LocalSystem” to a local user account in the “MSSQLUser” group, in order to remove un-needed rights from SQL service in the system. (I could not use a domain account at this time, so I had to go with a local account for testing.)

As this is a something trivial, I tought there would be no drawbacks. But not this time. Right in the first restart of the service I was greeted with two strange error messages on SQL Server Logs.

Error: 8319, Severity: 16, State: 1.
Windows kernel object 'Global\SQL_90_MEMOBJ_MSSQLSERVER_0' already exists. It's not owned by the SQL Server service account. SQL Server performance counters are disabled.

and right after it:

Error: 3409, Severity: 16, State: 1.
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.
Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

The “resource governor” part of the message worried me in particular, as it is a important resource from sql2008 that I want to implement.

After checking permissions, re-installing sqlctr.ini and even registry-editing nothing seemed to remedy my situation. Until I realized that the new account used to start the service never had a “logon/logoff” on the system. Then I logged-in using this new account, waited for the system to create all local profile files, and logged-off. A quick restart of MSSQL service later, and the performance counters were back! No more worries about perfmon or resource-governor problems.

I know this is a strange behavior, and this kind of “login/logoff” stuff should not be required these days, but it worked for me.

PS: This issue only affects SQL2008/Windows2008 as I tried to make it happen on SQL2005/Windows2003 without success.

cya!

Update 1: even before posting this issue on the blog, I had another problem with it. Testing on my personal VMs (stand alone, out of any domain) worked, but on servers that are part of a domain aparently this is not enough to solve the problem. A little more research pointed that this could happen with domain accounts too. Investigating gpedit.msc I saw some strange SIDs in the groups where SQL groups should be, so I believe this may be some sort of user rights assignment issue and I’ll check it in more detail later. More links on the subject:

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/5f9b6250-b107-41e8-bb63-632c34c2b2db/

http://www.sqlservercentral.com/Forums/Topic424661-146-1.aspx#bm1318401

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/3a0bf9e2-856f-4887-883b-bbad8b763dc5/

Concerning service accounts, as recommended by Microsoft:

http://msdn.microsoft.com/en-us/library/cc281849(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms143504(v=sql.105).aspx

Update 2: after a while, we had a chance to reboot our server on the domain. Now the performance counters are working again. I did not change any configuration or right assignment of the account used to start the service, except for loggin-in and loggin-out of the server before the restart. So, if you face this issue, be sure to logon-logoff with the service account and restart the server to solve it.