Most every DBA I know uses sp_who2, an undocumented stored procedure that shows locking, blocking, who is on, what they are doing and so on.
At TechEd on Monday (I could only go for one day this time) I met up with my friend Adam Mechanic – a SQL Server "Most Valuable Professional" (MVP) who has created a fantastic stored proc that does so much more, called "Who is Active". It not only has static information, but you can pass it parameters like a retry interval so that it runs continuously, sends its results to a file and much more.
That’s typical of the work the MVP’s do. In fact, combined with all of the resources you have at Microsoft, you have a huge virtual support network to help you be more successful. You just don’t get that with a LOT of other tech companies.
Anyway, you can find his code here: http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx – it works with SQL Server 2005 and 2008.