Sample Header Ad - 728x90

TempDB Log Space and ACTIVE_TRANSACTION

8 votes
2 answers
44931 views
Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space. However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive. I looked at what the log_reuse_wait_desc was and found it to be ACTIVE_TRANSACTION I started to wonder if for some reason the log file was filling up and the auto grow not kicking in, and after some research I found that the log file should still grow even during an ACTIVE_TRANSACTION. I found an article on a similar issues where the tempdb log ran out of space: http://sqltimes.wordpress.com/2014/07/05/sql-server-error-messages-the-transaction-log-for-database-tempdb-is-full-due-to-active_transaction/ Here they issued a CHECKPOINT to resolve the issue on tempdb. I know a CHECKPOINT flushes dirty pages to disk, however I do not understand how this would fix the ACTIVE_TRANSACTION problem? Furthermore I also do not know why we get this alert when there is plenty of space. Is there a situation where a tempdb can fill and auto grow not work for some reason?
Asked by Tom (1569 rep)
Oct 7, 2014, 01:19 PM
Last activity: Jul 8, 2024, 04:13 PM