Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Activity Monitor in SQL Server 2012 SP1

Activity Monitor in SQL Server 2012 SP1

Rate This
  • Comments 3

In SQL Server 2012 Service Pack 1 few changes were made in Activity Monitor. We had to stop using PerformanceCounterCategory class to get information about number of logical CPUs on the remote machine and switched to using more accurate information available via WMI (Win32_ComputerSystem.NumberOfLogicalProcessors if you’re looking for technical details).

Unfortunately it caused problems for existing customers in few very specific situations. Few bugs were reported by our customers:

https://connect.microsoft.com/SQLServer/feedback/details/770897/after-installing-sp1-sql-server-2012-activity-monitor-gives-access-denied

https://connect.microsoft.com/SQLServer/feedback/details/771843/ssms-2012-sp1-activity-monitor-not-working-against-2008-r2

In all cases problems were seen when running SSMS Activity Monitor from SQL Server 2012 SP1 trying to get CPU utilization graph from the remote servers running SQL Server 2008 R2 on Windows Server 2003. We didn’t see any reports when connecting to more recent version of SQL Server and more recent version of Windows Server.

There are two different errors you might see: “Access Denied” or “Invalid Query”.

If you are experiencing "Access Denied" errors you need to enable DCOM rights on the remote machine to allow WMI connections from the machine you’re running SSMS on. Here are the steps to do this:

  1. Click Start, click Run, type DCOMCNFG, and then click OK.
  2. In the Component Services dialog box, expand Component Services, expand Computers and then right-click My Computer and click Properties.
  3. In the My Computer Properties dialog box, click the COM Security tab.
  4. Under Launch and Activation Permissions, click Edit Limits.
  5. In the Launch Permission dialog box, select your user and group in the Group or user names box. In the Allow column under Permissions for User, select Remote Launch and select Remote Activation, and then click OK.
  6. Under Access Permissions, click Edit Limits and give Remote Access to your user.
  7. Go to DCOM Config(Expand My Computer), find "Windows Management Instrumentation", right-click and select Properties.
  8. In the Security tab, click on Edit under Launch and Activation Permissions, and give your user Remote Launch and Remote Activation. 

For more information, see Securing a Remote WMI Connection http://msdn.microsoft.com/en-us/library/windows/desktop/aa393266(v=vs.85).aspx

If you’re seeing “Invalid Query” error message most likely your remote machine doesn’t have updated WMI objects. This can be easily fixed by installing the following hotfix (on the remote machine): http://support.microsoft.com/kb/932370 

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
  • I get the access denied error, despite the Windows 2008 R2 server and not 2003.

    The SQL server is 2008 (R1).

    SQL 2008 tools activity monitor is fine, just not the 2012 sp1 version.

    lteska@mayo.edu

  • Thanks! This fixed our issue.

  • Thanks for the post. This fixed our issue in Windows 2012/SQL 2012 SP1.

Page 1 of 1 (3 items)