13

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.

Chris S
  • 77,337
  • 11
  • 120
  • 212
mika
  • 1,466
  • 2
  • 12
  • 18

8 Answers8

20

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
mika
  • 1,466
  • 2
  • 12
  • 18
5

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.

Renaud Bompuis
  • 519
  • 2
  • 7
  • 15
  • 1
    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 08 '10 at 11:43
2

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.

Carlos
  • 121
  • 3
1

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.

user82106
  • 11
  • 1
1

I thought I would post my experience with this issue.

Symptoms - SQL Server 2008 R2, on Dell machine, suddenly suffered huge performance degradation. User applications became very slow when performing queries. Activity monitor would start - but the above process timeout error would occur if you tried to open the process list.

Restored backups of the databases performed fine on a second server with half the memory. Restoring these same backups to the original server did not resolve the problem.

I ran dbcc's on all databases, rebuilt indices. Forced re-create of the Windows page file. Tried restarting SQL server. Tried rebooting the server. None of these worked. Reset the performance counters as described above - this improved the server CPU usage but did not resolve any problems.

Our network admin wanted to rule out hardware issues. He updated the device drivers for the RAID controllers, then powered down the server. He used a power sequence from Dell to purge memory, this involved disconnecting from the power supply. After restarting, the server performed fine.

We believe the power cycle resolved the issue, and that the underlying problem was with the hardware memory.

John
  • 11
  • 1
0

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.

0

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!

Ed Sykes
  • 248
  • 2
  • 3
0

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!

Raul
  • 1