December 30, 2015

Activity Monitor fails- Microsoft SQL Server Management Studio

Error Details:

Getting below error when trying to open process details or checking long queries in Activity Monitor window in SQL server R2 Enterprise Edition.

The Activity Monitor is unable to execute queries against server [xxxx 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.

Other Information:

Unable to find SQL Server process ID [PID] on server [xxxx server] (Microsoft.SqlServer.Management.ResourceMonitoring)

Solution :

This error exist because one of your service is not enabled on server.Name of Service is Performance Counter DLL Host.

Just go to Services in control panel and find service Performance Counter DLL Host set it automatic and start the service.

After doing this you need to close sql and reopen again.

If still error not resolved then go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PerfProc\Performance

by in registry editor using regedt32 command. Find key Disable Performance Counters and set value to zero then restart server.

I hope problem will resolve.