Sql-server – Urgent help with TempDB log file growth thestery

performancesql serversql server 2014tempdbtransaction-log

I need some urgent guidance for a tempdb issue for past last week:

The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down after 10 or so hours.

I have used various queries listed here but nothing shows up as in that duration there is no long running transaction.

Queries of mix workload keeps coming and going. There is no stuck transaction as such.

The user databases on this instance are all part of Log shipping setup and log backups happen every 15 mins.

In addition i added the Xevent

CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT 
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

Nothing gets captured even in XE and its just blank in those 10 hours.

I have no clue what is eating my tempDB log as none of the queries or XE gets me the data. I have never seen this thing and generally get database growth using XE but here i am just clueless.

I am running DBCC SQLPERF(logspace) and i can see log used just keeps increasing by 1 Gb every 5 mins or so. We have TempDB log set to max 200 GB but by 10 hours its almost 160-180 GB and then suddenly comes down.

Running below query almost every time gives ACTIVE TRANSACTION.

SELECT log_reuse_wait, log_reuse_wait_desc 
FROM sys.databases d 
WHERE database_id = 2;

Please help what i might be doing wrong and why i cant see any transaction using TempDB log file usage?

Update- As requested in answer from @J.D, i checked and verified that there is no database mail or Service broker is being used internally to cause the growth:

However over last week i see the growth in log file of tempdb is zig zag and the trend of that zig zag growth is bit higher:

I need to help understand :-

How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching lmoast max 90-95%.

I confirmed there is no manual shrinking going on. Could there be multiple processes contributing as this is OLTP server with tempdb usage throughout the day. Just dont understand how in last week it has grown near its max capacity?

thanks

Best Answer

What you are you seeing is by design for tempdb Transaction Log file. I wish Microsoft had better documentation on this.

How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching almost max 90-95%.

From the First reference article:

Tempdb is not recovered in the event of a crash, and so there is no need to force dirty tempdb pages to disk, except in the case where the lazywriter process (part of the buffer pool) has to make space for pages from other databases.

A checkpoint is only done for tempdb when the tempdb log file reaches 70% full – this is to prevent the tempdb log from growing if at all possible (note that a long-running transaction can still essentially hold the log hostage and prevent it from clearing, just like in a user database).

Of course, when you issue a manual CHECKPOINT, all the dirty pages are flushed, but for automatic checkpoints, they’re not.

I suggest you monitor your tempdb Transaction Log usage and set it to a reasonable size that under normal workload it does not have to grow and shrink.

I do not know exactly why your tempdb logfile filled to 100%. There has to be a reason why the log file did not get truncated.

Will indirect checkpoint help?

In most cases no.

Any non-logged “bulk” operation that qualifies for an “eager write” in tempdb is not a candidate to be flushed by the recovery writer (the internal thread that runs the indirect checkpoint).

This raises an important question: which data load operation is minimally logged on tempdb? This is important to know because minimally logged operations on tempdb will not be flushed by the indirect checkpoint. The following list can be used to assist you in understanding which load operations on tempdb will be minimally logged and which will not.

For details read Tempdb – Here’s a Problem You Didn’t Know You Had by Fabiano Amorim

I noticed this question is for SQL Server 2014, if someone is reading this for SQL Server 2016 read the below documentation by Microsoft.

Database Checkpoints (SQL Server) - check the "Indirect Checkpoint" section.
Change the Target Recovery Time of a Database (SQL Server).
Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler by Parikshit Savjani

Reference:

What does checkpoint do for tempdb? by Paul Randal
See question and answer section Inside the Storage Engine: What’s in the buffer pool? by Paul Randal

Related Solutions

Sql-server – Identify cause of unusual transaction log growth

This SQL shows queries with most "logical_writes":

SELECT TOP 100
    st.text,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
ORDER BY total_logical_writes desc
OPTION (RECOMPILE);

This T-SQL could help to find direction, but it is not really what you asked for, as logical write != write. and logical write != log write.

It could be very very interesting to find more complete answer to your question.

Sql-server – Issues with TempDB mdf file ever increasing

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes.

Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file initialization enabled. You should really try to pre-size your file so that they're big enough to accommodate your busiest workload, and avoid growth events altogether. This isn't always practical, but in lieu of that, the growth size should be much larger. You really want this to be a rare and isolated event, not one that is occurring constantly. What is the point of keeping tempdb small, when it's going to use more space eventually? Are you going to lease the space out temporarily to the highest bidder, then evict them?

Also, why is there only one tempdb file? This is a common source of contention. Typical wisdom suggests to start with 4 files, even if they're all on the same disk. This can drastically reduce contention especially when multiple concurrent processes are trying to create objects or otherwise use space in tempdb. You may also want to look into enabling trace flag 1117 (which ensures that all of your data files grow at the same time) and trace flag 1118 (which changes extent allocation). Links about these below.

None of this will solve your core issue, of course - lack of (or concern over) disk space. If you don't have enough disk space to support the current usage of your system (whether it's the system or your users), get more disk space, or move the system. You might be able to find some of the culprits (see this answer for some common ones), but you won't be able to squash them all.

You might also find these things useful:

this blog post by Jason Hall
this survey from Paul Randal
this TechNet article on optimizing tempdb
this squashed myth from Paul Randal
this discussion on TF1118
this blog post from Paul White, explaining temporary object caching
this question about determining version store usage
this answer (also linked above) on determining contributors to tempdb log usage, and common causes for tempdb usage in the first place
there are also three "Linked Questions" in the right sidebar of the above answer that may be useful
this documentation on contributors to tempdb size
this question, also about contributors to tempdb size
this tip about tempdb configuration best practices on mssqltips.com

And also, now that we know you are using Service Broker, you may want to read these two pages which might help explain why your conversations aren't clearing out:

Best Answer

Related Solutions

Sql-server – Identify cause of unusual transaction log growth

Sql-server – Issues with TempDB mdf file ever increasing

Related Question