we allocated 195GB for tempdb data
and 95GB for tempdb log

Simple recovery model

the log file got to 70% capacity and with the long weekend coming up we were a little concerned so we bounced sql and zero'd it out, that was last night.

This morning I notice the tempdb log is already @ 35% capacity (about 33GB used).

What can I do to find out the offending query(ies)?

My first instinct was that we might have a long running query that is preventing the log from being flushed, but:

returns No active open transactions


SELECT * FROM sys.dm_tran_database_transactions
where database_transaction_begin_time is not null
ORDER BY database_transaction_begin_time

returned 1 transaction_id from 10 minutes ago and the next oldest, 4 minutes ago, 2 from 2 minutes ago and then 15 within the last minute.

Should I start there? How do I resolve a transaction_id to a spid?

Also should we be allocating more drive space for tempdb log? I thought 95GB was excessive, but now I am thinking I could be wrong.