SQL SERVER – 2008 – Location of Activity Monitor – Where is SQL Serve Activity Monitor Located

I received question from Aloke Sinha after reading my article SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users.

Hello Pinalbhai,

Thank you for your post about activity monitor, but I can not find activity monitor under Menu — Tools. How to activate it?

[Other unrelated information removed]

Take care,
Aloke Sinha

The reason I decided to write about this subject is because I totally understand why Aloke is confused here. Activity Monitor can not be activated from any menu from top menu bar. There are two different methods to activate Activity Monitors.

From Standard Toolbar (Standard Toolbar can be made visible from here : Menu >> View >> Toolbar >> Standard) and from “Right Click” of SQL Server Node.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users

This article is outcome of the technical discussion of activity monitor and its behavior with my friend and SQL Expert Tejas Shah. Tejas told me that he does not like to re-write content from MSDN but rather prefer to write real life scenarios, as that prepares him to become better SQL Expert. While discussing about Activity Monitor he informed that activity monitor throws an error when there is permissions issue. He has even blogged about how to give permissions to user to launch activity monitor on his blog . Tejas asked me to write on the same subject for SQL Server 2008. Here is the article covering the discussion I had with Tejas.

I have user called ‘ActivityUser’ when turned on Activity Monitor (while logging in with user ActivityUser’) it does not have show anything in Activity Monitor.

The issue here is permissions issue. If user ActivityUser is given all the necessary permission it will start showing up data in Activity Monitor. Activity Monitor is new tool in SQL Server which displays activity in five sections. 1) Overview, 2) Processes, 3) Resources Waits, 4) Data File I/O, 5) Recent Expensive Queries. It is one of the new and very useful tool introduced by SQL Server.

Activity Monitor captures all the information at server level. For the same reason we need to give “View Server State” permission to user name to view data of Activity Monitor.We can give permission either using T-SQL or using SSMS.

T-SQL to give permission to user to view Activity Monitor:

SSMS to give permission to user to view Activity Monitor:

Once permissions is given to user, it displays the data in Activity Monitor. Click on images to enlarge the images.

Additionally, note that if you are user belonging to sysadmin role, you can always see all the data in Activity Monitor without additional permissions.

Reference : Pinal Dave (http://blog.sqlauthority.com)