SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'” 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 &lt; @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.
This is happening because the disk is full. 

I was given bad information. The primary DBA was out when I had this problem and I asked one of our secondary DBAs to check the disk space on the drive where the tempdb log file lives because I don't actually have access to this server. The secondary DBA told me that there was 25GB free space on that drive. In actuality, there was less than 10MB free space.