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.

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer, Windows and tagged , , , , . Bookmark the permalink.

4 Responses to Missing performance counters on SQLServer 2008

  1. Nick C says:

    Thanks for blogging about this, it saved the day for me after a service account change. To confirm, after the service account change this happened on 3 servers, all Windows Server 2008R2 Enterprise Edition, 2 of them with SQL Server 2012 Enterprise Edition SP1 and one with SQL Server 2008R2 Enterprise Edition. Re-starting the server itself solved the problem. 🙂

    • mauriciorpp says:

      Glad to know it helped! I know that restarting servers is not a thing we expect to do these days, but there are some situations that we just can’t avoid a system boot. Thanks for reporting that it happened on SQL2012 too. cya!

  2. Brian says:

    thanks for sharing this info. Saved a lot of hassle when I started seeing these errors in the logs when we changed from local to domain accounts on the SQL Service Account.

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