FYI: moved from Stack Overflow https://stackoverflow.com/questions/56350491/sql-server-tempdb-log-file-isnt-autogrowing-instead-getting-error-the-transac
I'm using SQL Server 2016 and am encountering this error intermittently:
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'
It appears that this happens when a transaction is opened and then an expensive tempdb operation is done within the transaction. It seems to me that the tempdb log file should be growing, but it isn't, and instead I"m getting the error above.
I'm able to reproduce this by using the code below. The error comes up everytime I run the code below on the server having this issue. I tried reproducing this on a different SQL Server and couldn't.
BEGIN TRAN SELECT * INTO #temp FROM sys.all_objects DECLARE @iLoops INT = 1000 DECLARE @i INT = 0 WHILE @i < @iLoops BEGIN INSERT INTO #temp SELECT * FROM #temp SET @i = @i + 1 DECLARE @rowCount INT = (SELECT COUNT(*) FROM #temp) PRINT CONVERT(VARCHAR(16), @i) + ' of ' + CONVERT(VARCHAR(16), @iLoops) + ', rows in #temp: ' + CONVERT(VARCHAR(16), @rowCount) END DROP TABLE #temp ROLLBACK
As the reproduction code is running, I'm monitoring the size of the tempdb log file by using this query (which I got from https://www.sqlshack.com/monitor-sql-server-tempdb-database/):
SELECT instance_name AS 'Database', [Data File(s) Size (KB)]/1024 AS [Data file (MB)], [Log File(s) Size (KB)]/1024 AS [Log file (MB)], [Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)] FROM (SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)') AND instance_name = 'tempdb' ) AS A PIVOT (MAX(cntr_value) FOR counter_name IN ([Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)])) AS B
The error appears to happen when the tempdb's log file's used size reaches the allocated size. Why isn't the log file autogrowing? Looking in SSMS, the tempdb log file is set to autogrow and has no maximum size. The drive on which the log file lives has over 20GB of free space. The tempdb database is using the simple recovery model.