Urgent help with TempDB log file growth mystery 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
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 &quot;Indirect Checkpoint&quot; 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