Activity Monitor always Paused in SSMS 17.8.1

Question

Whenever I open the Activity Monitor in SQL Server Management Studio 17.8.1(14.0.17277) the overview always switches to (paused). Even if I choose resume, it quickly changes back to paused.

This happens on a variety of SQL Servers and SQL Server versions (2005 through 2016) so I don't believe it is a conflict with old vs new SQL Setups.

I can run Activity Monitor in SSMS 2012 (11.0.2100.60) on the same servers with no error which confirms that the service is actually running and functional.

Any help or insights would be appreciated. I'm not a fan of switching back and forth between two management studios if I can help it. (I uses 17 so I can have context menus when right clicking on items in SSMS which wont work on 2016 servers in older versions of the studio).

Origin

Have you tried to close the SSMS and reconnect to server? and restart SQL agent might help – Peace

I see the same behavior. – Hinder

I have restarted SSMS, Reconnected to the server (as different users too). Two servers have been fully restarted (part of a scheduled maintenance) andt his still continues. but only in SSMS 17. In SSMS 2012 it works just fine. – Origin

I have 5 different instances installed and all of Activity Monitors all start with paused and will not resume. I connect as a server administrator. and 2008R2, 2012, 2014, 2016, and 2017 all have the same behavior. I did reinstall the latest SSMS 17.9.1 and after a reboot, all are still paused. – Mcfarlane

I also tried the SSMS installed on the server and found that they throw an error. The Activity Monitor is unable to execute queries against server x\MSSQL17. 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: Invalid class (System.Management) – Mcfarlane

Answer 1

I setup a basic SQL login and found that activity monitor was permanently paused for this login. Then I granted this login the "View server state" permission and activity monitor now works. To do this, open up the Security and Logins folders for the relevant server instance, right click a login and choose properties. Choose Securables and you should see all Permissions listed in the bottom pane. Put a tick in the grant column next to "View server state".

Nevels

Thank you! This resolved the issue for me. We experienced this issue with an AWS RDS SQL Server 2019 version and with SSMS 18.12.1. – Photomural

Answer 2

Run as administrator helps, but I only see this happen on SQL clusters

However I have found the following somewhere, can't remember where. And I added the AD group with Sysadmin rights using these steps 1-5

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.

Seems I don't need to follow step 6-8, so I have not tried these.

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.

Vervain

and did it on all cluster nodes – Vervain

Answer 3

I experienced the same as Izulien (running v 17.7 of SSMS), in out production environment with my personal user. Reconnecting to dbs and restarting SSMS did not help.

However I did have access via the sa user to our dev-environment. Using the sa user did the trick in dev, and the same applied for our production environment, leading me to assume that this is connected with privileges/roles on my user.

Urea

I'd love to hear if you find out what specific privilege needs to be added for the new versions of SSMS. I'm now running 18.0 GA and it is still an issue. I have access to SA on a few of these servers so I will look at that tomorrow to see if it actually clears the issue. – Origin

Surely is related to user privileges. For me one specific server works, where I have full access. In another, with less privileges, it does not work – Glyph

Answer 4

In the environments that I manage, that only happens when I am using SSMS in a different computer, other than the server where actually the SQL Server ENGINE is installed. That is: SSMS client on a PC, and SQL Server engine/instance in a server. For me, 99% of the time this means: I am in Florida running a local PC Virtual Machine on my personal iMacPro, running SSMS, accessing a SQL Server server in Chicago via VPN.

So, I tend to believe this may be some sort of network timeout that happens..?

This is just a theory of mine. Because, if I actually Remote Desktop into the SQL Server itself and run SSMS locally in the server the Activity Monitor does not pause.

My two cents. Maybe someone can unravel this better.

EDIT: Also, I notice that it's when I expand the processes panel that shortly thereafter it pauses. If I leave the processes panel collapsed it does not happen, or at least not as promptly. AND, interestingly, if I open Activity Monitor, then I do NOT immediately open the process panel and let the graphs run for a while, say, two minutes, and THEN I open the process panel it does not pause anymore.

It seems to be that the initial population of the graphs AND the initial population of the process panel at the same time that cause the problem. At least that's the case for me across the SQL Servers I manage.

R.

Holsinger