Shrink TempDB in SQL 2000

Question

Post reply

Shrink TempDB in SQL 2000

kalirajank

Old Hand

Points: 347
More actions
July 24, 2008 at 7:14 am

#210428

Our application using TempDB intensively makes tempdb size as 12 GB. if we go for shrink the tempdb data file to some min size, will it be affect the performance of the server process:discuss:

Viewing 10 posts - 1 through 9 (of 9 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

It will probably grow back out to 12g anyway. You should give temp DB some room to grow for optimal performance.

kalirajank Old Hand Points: 347 More actions · Answer 2

I can allocate necessary space to grow Tempdb data file space.

Will it be problem, if we have tempdb having this size as 12 GB ?

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

Generally should not be a problem, and whenever you restart SQL it goes back to the original size anyhow. Only worry I would have about tempdb size is to have enough space on the hard drive for the largest size.

kalirajank Old Hand Points: 347 More actions · Answer 4

Even I am restarting the server by-weekly the tempdb size doesn't go back original size. It remains the same size what we have earlier. tell me if any reason it is not drop and recreate in original size.

How do we identify the default size of the database

steveb. SSC-Forever Points: 46733 More actions · Answer 5

The Tempdb will start as the intial size.

Change this in properties and then restart.

kalirajank Old Hand Points: 347 More actions · Answer 6

Can you provide me the properties to change for the database tempdb starts with minimum size

nary SSCrazy Points: 2869 More actions · Answer 7

By Default Tempdb's size when Server starts is same as of Model database , but you can change as per your need to any amount but not less than the size of model Database. change the properties of database either from EM or QA . then restart the services change size will be effective then .

kalirajank Old Hand Points: 347 More actions · Answer 8

But in our server the tempdb could not get created with model database size as 1 mb. It is always start with old size they have like 12 GB.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 9

I assume you 'inherited' this database, rather than authoring it. I'd suspect that someone, before you, set the TempDB to start at 12 GB. Either that or you aren't fully stopping the database when you think you are, which would also cause it to retain its 12 GB (far fetched, but remotely possible).