tempdb ballooned

  • Hi,

    This morning our tempdb size shot up and filled up our drive. It went from around 2 gigs to 24 gigs. We failed over since we had to be back up. How can we monitor this kind of activity.

    Sam

  • Hey,

    I would suggest you set-up an alert in performance monitor to send an email, when data file size for tempdb is over a comfortable threshold, which provides ample opportunity for you to resolve before growth consumes all your drive space.

    You can use the Perfmon counter SQL Server Databases: Data File Size to achive such an alert.

    Thanks,

    Phillip Cox

  • I'd do what Phillip suggested and also you might want to ensure there are limits to how tempdb can grow. If you allow autogrow, you might set a "space holder" on the drive, a large 1GB or so file that sits there and you can delete if need be in an emergency.

  • Heh... sounds to me like someone wrote an accidental Cartesian join and decided to wait for it to complete...

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

  • We acutally found a reporting services query that was running against oracle and was pulling two tables - one with 15 rows, one with 500k. No joins...that was last weeks bonehead query.

    Now this!

    I've created the alert (which was a very good thing for me to do - I didn't know you could use performance counters in there!!!!). Need to do some research on what to do once we know it is happening. I assume there is probably some info out there on how to do it, so I'll get to it.

    Thanks alot!

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

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