TempDB - very large

Question

TempDB - very large

John Morgan-454160

SSC Veteran

Points: 201
More actions
March 13, 2008 at 3:52 am

#124442

Hi,
can someone offer me some guidance please? My tempdb has grown to 27GB, something I haven't been monitoring, and I need advise on how I can reduce it to a manageable size and how should I manange this DB in the future?
John M

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

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

John Morgan-454160 SSC Veteran Points: 201 More actions · Answer 1

I have over looked one thing, the size is not the actual size of the DB. But the allocated space, should of looked closer. The actual size is only a few MB, but the allocated space is taking a lot of resources. Should I set a maximum size in properties, data file of the tempdb.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Since tempdb is used for temporary objects - that means at some point since you restarted the server - you had one or more objects active that made the tempDB grow to that size. Often enough, something THAT big is due to a bad query/missing join stuff, etc.. that would cause very high cardinality; but it could also be a big load operation or some huge transaction (which probably needs to be broken up into smaller pieces).

The point though is that it needed that space. If you had limited the space - the process requiring that space would have aborted. Hunting down what made that happen would certainly be useful, but I'm not sure I'd be comfortable with just putting a max size in on tempdb. Really bad things could happen with that; of the two alternatives, the lesser evil in my book would be that tempdb would be a little bloated for a while (until I restart/reboot the SQL server).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Matt Miller (3/13/2008)
the process requiring that space would have aborted.

That would also make it easier to find... dummy that did it would scream out loud 😀

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (3/13/2008)
Matt Miller (3/13/2008)
the process requiring that space would have aborted.
That would also make it easier to find... dummy that did it would scream out loud 😀

And yet another troubleshooting technique....Kind of like whack a mole....:D

There's unfortunately no guarantee that they'd be the first OR the only one to scream (or am I not thinking that through?).....:w00t:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

The real users will show up at your desk in a panic... have the porkchops ready... 😀

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