CJSmith dot me

I dump stuff I find useful here

Find processes which have been using tempdb

Published / by Chris Smith / Leave a Comment

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).

SELECT *
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

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.

Leave a Reply