In the process of attempting to open Activity Monitor in Microsoft SQL Server Management Studio, you can experience and error message which says "SSMS activity monitor paused state".
Here at Ibmi Media, as part of our Server Support Services, we have helped numerous customers fix SQL related issues on a daily basis.
In this context, we shall have a look at what triggers this error and how to get rid of it.
Why does "SSMS activity monitor paused state" SQL error occurs?
SQL SSMS activity monitor paused state error is triggered when the "high level insight" option in the Operating System for the user accounts is not enabled.
Basically, All activities that takes place in the SQL Server is shown in the Activity Monitor. A high-level insight into the Operating System is necessary by the Activity.
Main factors which lead to "SSMS activity monitor paused state" error and how to fix this error?
Below we will discuss a few reasons why this error occurs and how to tackle them respectively.
1. When the high-level permissions are not enabled before attempting to make a remote connection to the SQL Server.
To resolve this issue, ensure that the elevated permissions option is enabled before attempting to make connection remotely. You can follow the procedure below to enable Launch & Remote Activation permissions on the remote Operating System;
i. To begin, Make a connection to the remote machine via RDP (Remote Desktop Protocol).
ii. Navigate from the Start Menu to the Component Services (DCOMCNFG).
iii. You will see in the left hand tree,an option named "Component Services" located under "Console Root". Expand the Component Services option followed by expanding "Computers" which you should right click on the "My Computer" and click on the Properties.
iv. From the Properties page, navigate to the COM Security tab.
v. Now you will see the " Launch and Activation Permissions" area where your can Edit Limits. Next, Click on the "Edit Limits" button and do the following;
a. From the Security Limits tab, look out for your user/group name. If it is not available there, do create one by clicking the Add button there.
b. As soon as you Add the user, select it by highlighting the user and ensure that permissions for the Remote Launch & Remote Activation are selected.
vi. Make a similar modification as in the previous step by clicking on the Edit Limits button in the Access Permissions area. Here your can add user/group if not available in the Security Limits tab. After making the necessary changes, effect changes by clicking Okay.
vii. From the My Computer in the left hand tree of the page, navigate to the DCOM Config. From here, go to the Windows Management and Instrumentation and click on Properties. Now you can Click on the Security tab located below the Launch and Activation Permissions area and click on the Edit button.
Again check if the user/group name is available under the Security tab. If it does not exist, click on the Add button to add a new user/group to the list.
viii. After adding the user,highlight it and ensure that it has both Remote Launch & Remote Activation permissions selected. To effect changes, save and then restart the Activity Monitor.
2. User accounts permissions not enabled by default.
Ensure that the necessary permissions are granted to the user accounts by giving them a "View server state" permission. Follow the following steps to implement this;
i. To begin, Open the Security and Login folders from the concerned server instance.
ii. Next make a right click on the login and click on its properties.
iii. Now select Securables and you will see the option relating to all the Permissions at the bottom of the page.
iv. Finish by ticking the grant column close to the View Server state.
3. SSMS 2012 (11.0.3339.0) is known to have a bug.
Ensure that you use a recent version of SSMS.
4. Ensure that the Remote enable is enabled in WMI.
Follow the steps to enable Remote enable option;
i. To begin, run the wmimgmnt.msc on the DB-server.
ii. In the WMI-Control (RM-click), open properties.
iii. Here you can open the Security tab and select CIMv2.
iv. Now you can add an account or group if not available and from the permission list, click on Remote enable.