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:
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?
Best Answer
As Max mentioned the alert probably fired just prior to the log growing/needing to grow. SCOM collects transaction log free space % although I am not sure at what threshold the alert will fire.
here is a quick example to show you what state tempdb is probably in when you get these alerts but no log file growth.
first create a database, set recovery to full, and back it up
now switch to that database, create a table, and run DBCC sqlperf(logspace) to check the size oand free space in your log file.
On my system I have logfile size of 0.9921875 and log space used (%) of 48.4245. Now insert some data into the table and run DBCC sqlperf(logspace) again. On my system 45 rows inserted gave desired results(number of rows inserted may need to be adjusted).
This time, the DBCC sqlperf output should show that log size is the same but log space used is just under 100%. In this case, SCOM would probably fire an alert that log space is low. There is no more activity going on to cause the log file to grow and (in this example) no tlog backup to free up used space. tempdb is in simple recovery, so your active transaction probably used up most of the available space and did not release it but there was not enough activity in tempdb to trigger log file growth thus causing the alert fire.
cleanup database when finished