One of the first tools that was a lifesaver in my role as an Accidental DBA was sp_whoisactive from Adam Machanic. The situation was SQL server was unresponsive and we were trying to sort out what was happening. The Activity Monitor in SQL Server Management Studio was timing out before it would return any data. Fortunately, sp_whoisactive is quick and light enough that we were able to get the results we needed. A bug in a vendor’s product had opened a transaction without bothering to commit. As the locks built up, SQL Server got slower and slower, other databases on the server started to lock up … there were a lot of unhappy people. It was easy to see the offending process, to kill it (not ideal, but it wasn’t committing anyway) and the server cleared up quickly.
Yes, I could have gotten the information from sp_who2, but sp_whoisactive presents more information and presents it more clearly.
Check out sp_whoisactive at http://sqlblog.com/files/folders/beta/entry42453.aspx