1

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.

temp log file autogrowth settings

closed as off-topic by Aaron Bertrand May 29 at 14:15

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

  • The premise of the question is based on an assumption which turned out to be false: The drive on which the log file lives has over 20GB of free space. – Aaron Bertrand May 29 at 14:15
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.