SQL Server 2008 Management Studio Activity Monitor

Question

Hi,

I tried to turn on the Activity Monitor using SQL Server 2008 Management Studio (SSMS) through the options window of the application (Tools | Options | Environment | General | At Startup).

I restarted SSMS and I am getting the following message:

"This operation does not support connections to Microsoft SQL Server Standard Edition version 8.00.2249."

I need to be able to monitor the processes and activities inside the database since I am investigating a particular application which takes a lot of time in its database data retrieval access and I am thinking it may be due to some locks or some processes.

How do I resolve this? Inputs highly appreciated. Thanks.

Answer 1

The Activity Monitor relies on DMVs that were introduced in SQL Server 2005. You cannot monitor a SQL 2000 instance. Your version number (8.00.2249) is for a SQL Server 2000, a product no longer supported (mainstream support retired 4/8/2008). Upgrade the instance to SQL Server 2008.

If you want to investigate a SQL 2000 instance, you'll have to rely on the old views and procedures: sysprocesses, sp_who, sp_lock. See INF: Understanding and resolving SQL Server blocking problems.

Answer 2

SQL 2000 does not have Dynamic management views that feed this info to the activity monitor

Run sp_who2 instead, it is not as rich as the activity monitor but it will tell you who is connected and who is blocking. You can then also run something like DBCC INPUTBUFFER (SPID) to get the first 255 characters of the statement executed by the sql connection