TempDB filling up

Question

TempDB filling up

Dan.Humphries

SSChampion

Points: 12125
More actions
July 17, 2019 at 3:28 pm

#3663808

Hello all,
We have a SQL Server 2012 Enterprise SP1 running our ERP system.   We have 15 production databases with approx 570GB in size. Our Tempdb has an initial size of 100GB and sits on a 450GB drive. Every day it seems to grow in size until all 450GB is consumed in approx. 2-3 weeks. I have tried numerous SQL statement to try an isolate what is using space but every query seems to return little results and show not queries with any high allocation.   For example
SELECT TS.session_id ,
TS.request_id ,
TS.database_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation User Objects MB] ,
CAST(( TS.user_objects_alloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation User Objects MB] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation Internal Objects MB] ,
CAST(( TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects MB] ,
CAST(( TS.user_objects_alloc_page_count
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15, 2)) [Total Allocation MB] ,
CAST(( TS.user_objects_alloc_page_count
+ TS.internal_objects_alloc_page_count
- TS.internal_objects_dealloc_page_count
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation MB] ,
T.text [Query Text]
FROM    sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T

returns only 34 rows and all of them show an allocation of 0.   However if I go to the shirk files of tempdb it shows that only 2% of the allocated space is available. I have tried every variation of the SQL script I can find on Google but nothing shows any allocation.   Some show additional rows for example one shows 277 rows with indication on what is running vs no currently executing but the allocated space is still 0.   Anyone have any suggestion or tricks they have used to track down issue in tempdb?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Here's the thing. Your tempdb is sized to the peak amount that is needed for your workload. This means that if you size it at 100GB, and on Friday there's a busy time that needs 450GB, the file(s) grow to 450GB. When the busy time ends, and objects clean up out of tempdb, the files do not shrink.

Database files in SQL Server DO NOT SHRINK. They are pre-allocated (or pre-grown) and the SQL OS inside SQL Server manages the use.

If your peak is 450GB, then do not shrink the files.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

Totally agree with Steve. I'll also add that if you have 450GB, the code is probably doing something wrong like an accidental many-to-many join.

The advice to not shrink it is correct except for 2 times...

You're trying to figure out what's making it grow.
You found what's making it grow and fixed it.

If it were me, here's what I would do...

Setup an alert to run something like sp_WhoIsActive when TempDB grows and save the output to a table for analysis. I've not tried your code above but, if it does what you want, then use that instead of sp_WhoIsActive.
Shrink TempDB down to a more reasonable size and wait for the next growth spurt.
Analyze the data you've captured to find the bad code.
Fix the code.
Go back to Step 2 to make sure it's fixed. Don't forget to empty the analysis table that the alert populates.
Leave the alert and analysis table active because it will happen again in the future.
Once complete, strut a bit because you deserve it. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)