I dump stuff I find useful here

Credit: Microsoft TechNet
On some occasions a few MS SQL databases have exhausted tempdb space, this was due to users leaving computers running for weeks on end and the program using different isolation levels or open transactions.
I use the following in SQL in see which process ID have been running the longest, as its a select statement it can of course be amended to your requirements (Adding an order by clause or just selecting the columns you require).

Run in tempdb

Transact-SQL

1

2

3

SELECT*

FROMsys.dm_tran_active_snapshot_database_transactions

ORDERBYelapsed_time_secondsDESC;

You should see something like the following, rather than kill of the transaction if it is a user try and educate the user to come out of transactions when finished.