TempDB changes initial size after shrinking

Question

TempDB changes initial size after shrinking

palmeralex

SSC Rookie

Points: 46
More actions
August 28, 2015 at 2:24 am

#398956

Hi
I've had a bit of a strange experience when shrinking tempdb. Before everyone points out how stupid it is to shrink it, please read through the entire thread and bear in mind I'm not a full time DBA.
I'd set up tempdb with 6 files, one for each processor and set the initial size as 1gb on each with unlimited autogrow of 100mb.
Three months later the tempdb had grown to 84GB are was taking over the whole drive. I looked at the properties and only around 100mb of the tempdb was being used.
It's difficult to restart this particular database so I thought I'd shrink the database. I used the GUI (tasks - shrink - database) and it successfully executed.
When I went back in to review the properties it has only shrank to 69GB and the initial sizes of the files had changed varying from between 10087mb - 11929mb.
Can anyone explain why this has happened and what should be done about it.
Cheers
Alex

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

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

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

palmeralex (8/28/2015)
Hi
I've had a bit of a strange experience when shrinking tempdb. Before everyone points out how stupid it is to shrink it, please read through the entire thread and bear in mind I'm not a full time DBA.
I'd set up tempdb with 6 files, one for each processor and set the initial size as 1gb on each with unlimited autogrow of 100mb.
Three months later the tempdb had grown to 84GB are was taking over the whole drive. I looked at the properties and only around 100mb of the tempdb was being used.
It's difficult to restart this particular database so I thought I'd shrink the database. I used the GUI (tasks - shrink - database) and it successfully executed.
When I went back in to review the properties it has only shrank to 69GB and the initial sizes of the files had changed varying from between 10087mb - 11929mb.
Can anyone explain why this has happened and what should be done about it.
Cheers
Alex

It sounds to me like the files were simply in use. You can't shrink what's being used. And, that thing about TempDB growing to 84GB? That's likely the result of a batch job running or a really bad ad hoc query but, in either case, you should find out what it is and fix 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.

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)

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

This is normal behaviour , once you shrink tempdb , sql tries to find out which parts of the files are in use , after ignorning these parts it releases the remaining space back to OS. The size of the temp db after shrinking the becomes the initial size and overrides the values set up initially by the DBA, more of it is explained in the link below.

http://enabledbusinesssolutions.com/blog/the-tempdb-why-shrinking-doesnt-always-work/

There is nothing wrong with shrinking tempdb , its the user databases that usually cause problems , tempdb grows and shrink constantly and as such needs the occasionaly DBA intervention.

I would suggest you setup alerts for disk space monitoring ,and enabled fast file inititializaion also lookup trace flag 1117 and 1118

Good luck

Jayanth Kurup[/url]