1

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)?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • hmm, doesn't look like this works in SQL 2005 or 2008. If possible, it'd be nice to have a single script that worked for all. Maybe I'll just profile the SQL2005 activity monitor and see what it does :)
    – BradC
    May 25, 2010 at 18:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .