Problem:
On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive
It was 32 GB but now it is 240 GB
Questions:
- What is the mechanism of TempDB log file usage ?
TempDB is in SIMPLE recovery model, what can delay TempDB log file truncation
(and cause its growth as a result) ?
- How can I know or be alerted when TempDB data files or log file are growing ?
Is there an Extended Event for this, or other means to know, and can alert be sent by email when growth happens ?
- Performance Monitor metrics
Log file may be 240 GB, but it is really empty at the moment
Is there a metric that can be used to monitor amount of used space INSIDE the TempDB data/log files at any given point in time ?
I can see there are Active Temp Tables
, Temp Tables Creation Rate
, Worktables Created/sec
, etc. but these do not show exactly what I would like to see
- Drive space monitoring
Is there native tool in SQL Server or Windows, that can alert me, when the free space on the TempDB drive reaches below 20%, 10% ?
- How to find out historically which session or task filled the TempDB data or log file ?
I can see which user/system sessions CURRENTLY consume at TempDB by querying this: sys.dm_db_session_space_usage
. But how can I know, which user/task consumed large portion of TempDB, say, 2 hours ago, without having to sit and query that view ?
sys.dm_db_session_space_usage
user_objects_alloc_page_count
- (minus) user_objects_dealloc_page_count
= the difference will be what session is CURRENTLY consuming (in pages) in TempDB ? Or it does not work that way ?
If you know at least few of those questions, your help is much appreciated