Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there any fix to get SSMS activity monitor working?

Problem:

SQL Server Activity Monitor fails with an error dialog:

TITLE: Microsoft SQL Server Management Studio
The Activity Monitor is unable to execute queries against server [SERVER]. Activity Monitor for this instance will be placed into a paused state. Use the context menu in the overview pane to resume the Activity Monitor.

ADDITIONAL INFORMATION:
Unable to find SQL Server process ID [PID] on server [SERVER] (Microsoft.SqlServer.Management.ResourceMonitoring)

I have this problem on SQL Server 2008 R2 x64 Developer Edition, but I think it is found in all 64bit systems using SQL Server 2008, under some yet unidentified conditions.

There is a bug report on this in Microsoft Connect, but it is not solved yet.

share|improve this question
    
-1, vote for close. Reading it three times I still fail to see a single question in there. –  TomTom May 14 '10 at 10:54
    
I'm having the same issue on x64 Win2008 with SQL Server 2008. Would like to know how to fix this too, –  Renaud Bompuis Jun 8 '10 at 0:33
add comment

7 Answers 7

up vote 10 down vote accepted

Check if SQLServer performance counters exist in the Performance Monitor. If not, manually rebuild all performance counters running lodctr /R command with administrative priviledges:

cd \windows\system32
lodctr /R
share|improve this answer
add comment

OK, I think I have found a solution.

The issue seems to be with the mix of 32bit and 64bit apps that need to query each-other.

If you enable the service Performance Counter DLL Host in the Services control panel, the Activity Monitor should now work.

share|improve this answer
    
This points to the right direction, that is, performance counters. I got the activity monitor working by rebuilding performance counters using lodctr /R, but the status of Performance Counter DLL Host (started/manual/disabled) does not matter in my case. –  mika Jun 8 '10 at 11:43
add comment

In the simplest case all you need to do is to restart the SSMS. I just had this problem with SSMS 2008 R2 running against 2005 server after I had lost network connection while the Activity Monitor was running. I tried a couple of tricks before I decided to try restarting SSMS and that's what helped.

share|improve this answer
add comment

If SQL is running on a Windows 2008 R2 server or cluster, go to the Performance Monitor application, expand the Data Collection Sets, then select the System Performance, if the arrow is green on the line below the menu just click on it. This will restart the counters, you may wish to do same for System Diagnosis collection set.

Then just refresh or open new connection to the SQL instance you wish to open SSMS Activity Monitor for, this should have solved your problem.

I just stumbled into this today. Of course, the error message saying Use the context menu in the overview pane to resume the Activity Monitor didn't help me in the least.

Hope this helps someone.

share|improve this answer
add comment

I was getting the same error message and viewed the Technical Details. That led me to the Microsoft.SqlServer.Management.ResourceMonitoring.dll. I decompiled the method that was throwing the error and after a bit of tracing through the code I found an area where a PerformanceCounter in the "Process" group was trying to be instantiated. Well I checked in Perfmon and that group wasn't there. Other counters were working but that one wasn't there. Looks like that group got disabled somehow.

How to fix it: Use regedit to find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PerfProc\Performance there is a key in there called Disable Performance Counters , delete it or set it to 0 You may need a restart after you change the key.

share|improve this answer
add comment

I can't comment yet hence the new answer...

I tried EightVans suggestion first

How to fix it: Use regedit to find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PerfProc\Performance there is a key in there called Disable Performance Counters , delete it or set it to 0 You may need a restart after you change the key.

And i still experienced the problem. Then i tried renaud's suggestion:

If you enable the service Performance Counter DLL Host in the Services control panel, the Activity Monitor should now work.

And i still experienced the problem. Then I tried Mika's suggestion:

Check if SQLServer performance counters exist in the Performance Monitor. If not, manually rebuild all performance counters running lodctr /R command with administrative priviledges:

Now the activity monitor is working!

share|improve this answer
add comment

I just do

EightVans suggestion first

How to fix it: Use regedit to find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PerfProc\Performance there is a key in there called Disable Performance Counters , delete it or set it to 0 You may need a restart after you change the key.

and Mika's suggestion:

Check if SQLServer performance counters exist in the Performance Monitor. If not, manually rebuild all performance counters running lodctr /R command with administrative priviledges:

And activity monitor is now running in my system!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.