How can I track down what's filling my 'tempdb' transaction 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.

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 = '';
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'''' + CONVERT(nvarchar(10), s.session_id) + N''''
+ N'''' + CONVERT(nvarchar(10), s.[pages]) + N''''
+ N'''' + COALESCE(t.[text], N'''') + N''''
+ N'''' + COALESCE(NULLIF(
SUBSTRING(
t.[text]
, r.statement_start_offset / 2
, CASE WHEN r.statement_end_offset ''
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''session_idpagestextstatement text'' + @msg_body + N'''';

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

StackExchange Database Administrators Q#210507, answer score: 2