I thought that I would just share a little stored procedure that I’ve been using in my day job since becoming an accidental DBA! It’s the WhoIsActive stored procedure by Adam Machanic. This stored procedure pulls together ALOT of information about the sessions that are happening on the instance that it’s run. It shows:

• How long the session has been active in dd hh:mm:ss:mss
• Session ID
• Query text
• Login Name
• Wait info (Server, session id and how long)
• tempdb allocations
• tempdb current
• session id blocking it
• reads
• writes
• physical reads
• memory used
• status
• open tran count
• percent complete
• host name
• database name
• program name
• start time
• login time
• request id
• collection time

Several times now the above information has given me exactley what I needed to determine if something has gone wrong, if a session is blocking a query or the session id that I need to kill if really needed.
You can get a copy of the script for MS SQL 2005/2008 here