Activity monitor is unable to execute queries against server

Question

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.

Solution 1

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

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[TomTom]

-1, vote for close. Reading it three times I still fail to see a single question in there.

[Renaud Bompuis]

I'm having the same issue on x64 Win2008 with SQL Server 2008. Would like to know how to fix this too,

[mika]

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.