Tempdb size getting high

[u/ManufacturerSalty148]

Hello all my colleagues

I know this is an old topic, but I still haven't found clear instructions on how to solve this one problem. I have a SQL Server 2017 where tempdb keeps growing to almost 80 GB. I know it's because of temp spills, but I want to collect the queries or sessions causing this tempdb consumption using extended events. I haven't found clear instructions or examples of extended events on any site, and I don't know what to do if I find queries consuming tempdb or what to check in the query plan.

[u/AutoModerator]

After your question has been solved /u/ManufacturerSalty148, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Complete-Fondant-202]

Switch on querystore

Leverage Erik Darlings sp_quickiestore which will show you tenmpdb usage on queries.

[u/ManufacturerSalty148 → u/Complete-Fondant-202]

No extended event out there ? option because there is commvault bakcup runinng i saw it create temp tables and other agent runinng on same vm for sql server so i need to collect whatever consuming space from tempdb

[u/SQLDevDBA → u/ManufacturerSalty148]

If you must use Extended Events, try Erik Darling’s sp_humanEvents

https://youtube.com/playlist?list=PLt4QZ-7lfQifgpvqsa21WLt-u2tZlyoC_&si=23_WqJeBfJOG9Tlx

[u/Splatpope → u/ManufacturerSalty148]

Wait what the commvault mssql agent messes with tempdb ?

[u/ITGuy_cricket → u/Splatpope]

Sounds wierd to me too

[u/ManufacturerSalty148 → u/ITGuy_cricket]

It's kinda weird, but Commvault was running on an instance, and after a week, I saw tempdb growth go really high. I stopped it for a week, and the growth stopped. Then the backup team asked me to turn it back on and insisted I do it. I saw with one query that it creates temp tables during log backup, which is weird, so I needed a way to record every transaction causing tempdb growth so I could prove it.

[u/ihaxr]

Do you have read committed snapshot isolation enabled on a database (RCSI)? That would cause long running queries, even if they're select queries, to grow tempdb due to large version store.

[u/ManufacturerSalty148 → u/ihaxr]

No i dont have it enabled 🤷‍♀️🤦‍♀️

[u/BackgroundHour5533]

Read Paul Randal’s postings on his SQLSKILLS website on tempDB and why it grows. It is not based on the size of your database, it’s more related to transactions.

[u/ManufacturerSalty148 → u/BackgroundHour5533]

Exactly, but it seems there's no good way to track growth with truncation related to it.

[u/FreedToRoam]

depends on how big is your database and how big is your largest table that is being queried repeatedly. 80gb might not be excessive ... it all depends

[u/TheGenericUser0815]

What this guy writes about SQL Server issues saved my ass several times. Maybe you might want to look at this: https://blog.sqlauthority.com/2018/09/17/sql-server-identifying-query-using-tempdb/

[u/Zagnut042 → u/TheGenericUser0815]

It took me many years and a conference he was presenting at to learn his name was not "Dave". I still giggle to myself every time I hit his blog.

[u/KickAltruistic7740]

Most likely a long running transaction or a SELECT query that needs to copy an entire table before sorting. Sp_WhoIsActive should be enough to monitor in realtime otherwise look at using sp_Blitz or query store

[u/CPDRAGIMESH]

68.70 68.90

Please use following queries: sys.dm_db_task_space_usage,

sys.dm_exex_requestd

[u/MonkeyDDataHQ]

Have you looked at ANY of the DMVs? Brent Ozar's first responder kit? The Query Store?

Why do you even care if tempdb gets to 80GB?

What problem are you trying to solve? 😩

[u/ManufacturerSalty148 → u/MonkeyDDataHQ]

The application connected to the SQL server, a no-code application, has had low tempdb usage for a long time. It was only after I noticed Commvault creating tempdb objects during each log backup that I started wondering why. I just want to catch it.

[u/MonkeyDDataHQ → u/ManufacturerSalty148]

So check the DMVs and the temp growth events. Those are part of the default trace.

[u/SonOfSerb]

It's ok as long as she's not driving.

[u/Hot_Skill]

The default trace has a details that is causing the Tempdb growth.

[u/ManufacturerSalty148 → u/Hot_Skill]

I had not considered this before; I appreciate you bringing it to my attention.

[u/kwellendorff]

download darling data performance monitor lite. the tempdb dashboard is great for monitoring growth. and it’s somehow free….

[u/ManufacturerSalty148 → u/kwellendorff]

My company's policy doesn't allow open-source applications, which is a bummer because Darling data performance monitor would really help me out.

[u/CanProfessional766]

First thing to check is whether tempdb is actually full or if it just grew to 80 GB and stayed there. SQL Server won’t automatically shrink it after the space is freed. For the queries grab the actual execution plan and look for spill warnings on Sort/Hash operators. That’s usually a good clue. Also check the indexes and statistics for those queries.

[u/mani_RitzyAI]

Have you tried just logging Adam Machanics "sp_whoisactive" into a table on short intervals and using that as proof?

[u/coffeDrinkerDave]

Reindexation in tempdb?

[u/ManufacturerSalty148 → u/coffeDrinkerDave]

No we dont do it

[u/Better-Credit6701]

Is it in full or simple? Are you using replication, always on, or log shipping? What is the size of the database in relation to the log file? On virtual hardware or on a 'real' server?

[u/ManufacturerSalty148 → u/Better-Credit6701]

Its full in always on , i am suspecting commvault has bug that increase tempdb so i want to record every tmpedb growth with query and query plan or query plan id i didnt find good extended events that can do that