Sql-server – “Classic” activity monitor in SQL Server Management Studio 2008

sql serverssms

Right now I'm using SQL Server Management Studio 2005 to manage all my SQL servers (2000, 2005, 2008). I'd love to be able to take advantage of all the cool new features in SSMS 2008, the only thing holding me back is the new DMV-based activity monitor:

  • We are still 40% SQL 2000, which doesn't have the required DMVs for the new monitors
  • I don't like it as much as the old one 🙂

So has anyone made a plugin for SSMS 2008 that emulates the old activity monitor? If not, what about a slick set of scripts that accomplish the same thing or better, and work across 2000-2008 (more than just sp_who2)?

Best Answer

Check out the built in (but undocumented I think?) stored procedures sp_MSset_current_activity and sp_MSget_current_activity. They should get you all the information you are looking for - just not quite as pretty as activity monitor.

Example usage:

declare @id int
exec dbo.sp_MSset_current_activity @id output

exec dbo.sp_MSget_current_activity @id, @option = 1
exec dbo.sp_MSget_current_activity @id, @option = 2
exec dbo.sp_MSget_current_activity @id, @option = 3
exec dbo.sp_MSget_current_activity @id, @option = 4, @spid = 51 -- locks per spid
exec dbo.sp_MSget_current_activity @id, @option = 5, @obj = N'sysobjects'

The get procedure has comments in it that outline the different available options.

Related Topic