tempdb ballooned

Question

tempdb ballooned

Sam Greene

SSCertifiable

Points: 5252
More actions
November 8, 2007 at 9:15 am

#76816

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

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

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

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 738808 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

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)

Sam Greene SSCertifiable Points: 5252 More actions · Answer 4

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!