Sql-server – How to track down what’s filling the ‘tempdb’ transaction log

errorssql serversql-server-2016tempdbtransaction-log

We have a large and fairly well tuned SQL Server 2016 Enterprise Edition. Given the number of cores we have, our tempdb is currently comprised of 16 2,950 MB files running on a ram disk with a 16 GB transaction log file. All of the files are set to not autogrow and, as a general rule, this has run fine.

We have recently started to get some random "transaction log for tempdb database is full" errors. There's no set time when this is occurring so it is probably some user interaction. Since all interaction is through stored procedures, it's most likely some strange set of parameters or data that's causing the problem but we are unable to track down exactly what might be causing it. Any suggestions that might help us identify the culprit are appreciated.

I have been using the information in How to identify which query is filling up the tempdb transaction log? to track this down. And that gives valuable information but it doesn't give me a way to actually determine what the spurious circumstances are that is causing the transaction log to fill up.

Is there some way to trigger a snapshot or log what is actually causing the issue. Worst case I can write a monitoring program that pings the server every half second and uses variations on those queries to capture anything that is huge and has an open transaction but that still doesn't guarantee that I will actually catch whatever is causing the issue.

Best Answer

You could use a SQL Server Agent Alert to automatically perform some operation whenever the transaction log crosses a percent-used-threshold.

By way of an example, the following automatically emails the results of one of the queries from Aaron Bertrand's answer on a question about how to identify which query is filling up the tempdb transaction log whenever the tempdb transaction log becomes 80 percent full.

USE [msdb]
GO

BEGIN TRANSACTION;

DECLARE @ReturnCode INT;
DECLARE @msg nvarchar(1000);
DECLARE @jobId BINARY(16);
DECLARE @DatabaseName sysname;
DECLARE @DBAEmailAddress nvarchar(100);
DECLARE @JobName sysname;
DECLARE @JobCommand nvarchar(max);
DECLARE @PerformanceCondition nvarchar(512);

/*
    Change the parameters below to suit
*/
SET @DatabaseName = 'tempdb';
SET @DBAEmailAddress = '<email address here>';
SET @JobCommand = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @msg_body nvarchar(max) = N'''';

;WITH s AS
(
    SELECT 
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count 
          + s.internal_objects_alloc_page_count) 
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count 
      + s.internal_objects_alloc_page_count) > 0
)
SELECT @msg_body = @msg_body + N''<tr><td>'' + CONVERT(nvarchar(10), s.session_id) + N''</td>'' 
    + N''<td>'' + CONVERT(nvarchar(10), s.[pages]) + N''</td>''
    + N''<td>'' + COALESCE(t.[text], N'''') + N''</td>''
    + N''<td>'' + COALESCE(NULLIF(
        SUBSTRING(
            t.[text]
            , r.statement_start_offset / 2
            , CASE WHEN r.statement_end_offset < r.statement_start_offset 
                THEN 0 
                ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 
            END
          )
          , ''''
        )
    , COALESCE(t.[text], N'''')) + N''</td></tr>''
FROM s
    LEFT OUTER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

SET @msg_body = N''<html><body><table><tr><th>session_id</th><th>pages</th><th>text</th><th>statement text</th></tr>'' + @msg_body + N''</table></body></html>'';

EXEC msdb.dbo.sp_send_dbmail @profile_name = N''DBA''
    , @recipients = N''mvernon@mpi.mb.ca''
    , @subject = N''tempdb task space usage''
    , @body_format = N''HTML''
    , @body = @msg_body;
';

SET @ReturnCode = 0;

/*
    Add an operator to receive email alerts
*/
IF NOT EXISTS (
    SELECT 1
    FROM dbo.sysoperators so
    WHERE so.name = N'DBA'
    )
BEGIN
    EXEC msdb.dbo.sp_add_operator @name=N'DBA'
        , @enabled = 1
        , @email_address = @DBAEmailAddress
        , @category_name = N'[Uncategorized]';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "DBA" operator.';
    PRINT @msg;
END
ELSE
BEGIN
    SET @msg = N'DBA operator already exists.';
    PRINT @msg;
END

/* Add a job category*/
IF NOT EXISTS (
    SELECT name 
    FROM msdb.dbo.syscategories 
    WHERE name = N'Reliability' 
        AND category_class = 1
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
        , @type = N'LOCAL'
        , @name = N'Reliability';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    PRINT N'Added "Reliability" job category.';
END
ELSE
BEGIN
    SET @msg = N'Job category "Reliability" already exists.';
    PRINT @msg;
END

/*
    Add a job that performs a backup of the target database's transaction log
    This should free up space in the transaction log, assuming nothing else
    is preventing re-use of virtual log files, such as transactional replication,
    Database Mirroring, participation in an Availability Group, or an open
    transaction.
*/
SET @JobName = @DatabaseName + N' - log space usage' ;
IF NOT EXISTS (
    SELECT 1
    FROM dbo.sysjobs sj
    WHERE sj.name = @JobName
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @JobName
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Reliability'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "' + @JobName + '" job.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name = N'email space usage report'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority = 0
        , @subsystem = N'TSQL'
        , @command = @JobCommand
        , @database_name = @DatabaseName
        , @flags = 0;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "email space usage report" job step.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Job is configured to start at step 1.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
        , @server_name = N'(local)';

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
END 
ELSE
BEGIN
    SET @jobId = (
        SELECT sj.job_id
        FROM dbo.sysjobs sj
        WHERE sj.name = @JobName
        );
    SET @msg = N'"' + @JobName + N'" job already exists.  Using that job''s JobID.';
    PRINT @msg;
END

IF @jobId IS NOT NULL
BEGIN
    /*
        Add an alert to fire the above job whenever the Transaction log crosses 80% full
    */
    SET @JobName = @@SERVERNAME + N' ' + @DatabaseName + N' : 80pct TxLog Alert';
    IF NOT EXISTS (
        SELECT 1
        FROM dbo.sysalerts sa
        WHERE sa.name = @JobName
        )
    BEGIN
        IF SERVERPROPERTY('InstanceName') IS NOT NULL
        BEGIN
            SET @PerformanceCondition = N'MSSQL$' + CONVERT(nvarchar(128), SERVERPROPERTY('InstanceName')) + N':Databases|Percent Log Used|' + @DatabaseName + '|>|80'
        END
        ELSE
        BEGIN
            SET @PerformanceCondition = N'MSSQL:Databases|Percent Log Used|' + @DatabaseName + '|>|80';
        END
        EXEC @ReturnCode = msdb.dbo.sp_add_alert @name = @JobName
                , @message_id = 0
                , @severity = 0
                , @enabled = 1
                , @delay_between_responses = 300
                , @include_event_description_in = 7
                , @category_name = N'[Uncategorized]'
                , @performance_condition = @PerformanceCondition
                , @job_id = @jobId;

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
        SET @msg = N'Added "' + @JobName + '" alert.';
        PRINT @msg;

        EXEC msdb.dbo.sp_add_notification @alert_name = @JobName
            , @operator_name = N'DBA'
            , @notification_method = 1;
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
        SET @msg = N'Added job notification to email the DBA.';
        PRINT @msg;
    END
    ELSE
    BEGIN
        SET @msg = N'Alert already exists.';
        PRINT @msg;
    END
END
ELSE
BEGIN
    SET @msg = @JobName + N' not found.  Aborting.';
    RAISERROR (@msg, 14, 0) WITH NOWAIT;
END

COMMIT TRANSACTION
GOTO EndSave

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;

EndSave:
GO
Related Question