How can I identify the longest running transaction in my database? For example, I want to find out which process has held log space in a database for the longest amount of time.
The DBCC OPENTRAN command gives you the information you need by displaying information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions within the specified database. The command displays results only if an active transaction exists or if the database contains replication information. The command simply displays an informational message when no active transactions exist. I've found that glancing through all the commands that SQL Server Books Online (BOL) documents is a few hours well spent. You might not memorize all the commands, but you'll likely recall the appropriate ones when you run across a particular problem.