TempDB Size not Dropping

Question

TempDB Size not Dropping

plaid_draco

Mr or Mrs. 500

Points: 523
More actions
February 17, 2010 at 4:35 pm

#386329

I have a server with tempdb size that shot from 5GB to 10GB in a very short time. I know why the size jump occurred but am confused why it won't go back down.
I bounced the database services and even restarted the server but tempdb started up immediately using the full 10GB's it previously had been allocated. After the reboot I verified there were no jobs running, nor were there any open transactions. In addition I was the only connection to the server.
It's in simple recovery and not set to grow via %.
Ideas? I don't exactly want to brute force it via truncate and shrink.
Thanks much,
Michael...

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

plaid_draco (2/17/2010)
I have a server with tempdb size that shot from 5GB to 10GB in a very short time. I know why the size jump occurred but am confused why it won't go back down.
I bounced the database services and even restarted the server but tempdb started up immediately using the full 10GB's it previously had been allocated. After the reboot I verified there were no jobs running, nor were there any open transactions. In addition I was the only connection to the server.
It's in simple recovery and not set to grow via %.
Ideas? I don't exactly want to brute force it via truncate and shrink.
Thanks much,
Michael...

The only way that I've seen such a thing happen is when someone changed the initial size in properties. If someone did do that, then you may have to shrink it to get it back down to 5GB. I also have to tell you that I'll boot a busy production server with at least 10GB and that someone may have done you a favor by boosting it to 10GB. If you can afford the disk space, I'd simply leave it that way.

So tell us... what did you isolate the jump in size to?

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

koteswara kishore K. Ten Centuries Points: 1122 More actions · Answer 2

Then you have to enable Auto Shrink option in Server.

But this will impact on server/DB performance.

Instead of enable Auto Shrink avoid to use temp tables for large amount of data/records.

Thanks,

KK

plaid_draco Mr or Mrs. 500 Points: 523 More actions · Answer 3

I assumed after a service bounce tempdb wouldn't immediately consume up to it's maximum allocated size... especially afterward when I wasn't allowing connections other than my own.

The jump size on that database is 50 megs.