We have a alert threshold of tempdb usage on drive level at 90% and getting lot of alerts. But the first problematic point is the tempdb usage occupied whole drive of 500GB among Log file only occupied 95% and not able to increase the log file for other works on tempdb. While we checked the open transactions we seen sleeping sessions are there 2days and 1day before time stamps and not released the space to other transactions. I think it's worst behavior the sleeping sessions blocked the space and released to the others. We need the script for Is really those sessions are occupieing the LOG file space and not released them. (I came to know jdbc drivers sessions suddenly drops and so that the sleeping sessions are not released the space to other transactions). Can you provide Is there any script to identify the same.
Sql-server – tempdb log file usage huge by sleeping status sessions but not relased the space
sql server 2014tempdb
Related Question
- Sql-server – Understanding the usage of version store in tempdb
- Sql-server – should we keep the log file or the data file on the fastest drive
- Sql-server – Log for database tempdb is not available
- Sql-server – TempDB Transaction log not releasing space
- Sql-server – TempDB Log File Growth – questions
- Sql-server – Unexplained sustained space used in the TempDB log file
Best Answer
Use this script to determine tempDB usage per sessions :