TempDB filling up

  • Dan.Humphries


    Points: 12123

    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?


    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    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: 994239

    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...

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

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

    1.  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.
    2. Shrink TempDB down to a more reasonable size and wait for the next growth spurt.
    3. Analyze the data you've captured to find the bad code.
    4. Fix the code.
    5. Go back to Step 2 to make sure it's fixed.  Don't forget to empty the analysis table that the alert populates.
    6. Leave the alert and analysis table active because it will happen again in the future.
    7. 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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