Use the Activity Monitor component of Microsoft SQL Server Management Studio to get information about users connections to the Database Engine and the locks that they hold. Activity Monitor has three pages. The Process Info page contains information about the connections. The Locks by Process page sorts the locks by the connection. The Locks by Object page sorts the locks by the object name. Click the Filter button to apply a filter, reducing the amount of information displayed.
Use Activity Monitor when troubleshooting database locking issues, and to terminate a deadlocked or otherwise unresponsive process.
Open a query window in SQL Server 2005 Management Studio and execute the following statement.
-- Grant GRANT VIEW SERVER STATE TO << login name >> -- Revoke REVOKE VIEW SERVER STATE FROM << login name >>
In SQL Server Management Studio, connect to the server with Object Explorer, expand Management, and then double-click Activity Monitor.