var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: tempdb log troubleshooting
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.