Reduce the "startup" size of tempdb?

Question

Reduce the "startup" size of tempdb?

Eric Whitney

SSC-Addicted

Points: 428
More actions
June 29, 2004 at 10:35 am

#85836

How do I reduce the startup size of tempdb?
I currently have a 4000MB startup size and want to create 4 - 1000MB tempdb files based on our PeopleSoft environment and KB328551.

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

I'm not sure you can do that. TEMPDB is created using MODEL as a template (in fact, every database is created by using MODEL as a template).

Check to see if you can change MODEL database to be the way you want TEMPDB to appear. If you can do that, then TEMPDB will be created that way on start up.

-SQLBill

Eric Whitney SSC-Addicted Points: 428 More actions · Answer 2

Actually, it recreates the tempdb with the same size it last was. In a test, I stopped SQL, removed the tempdb data and log files and restarted the server. The new tempdb data file is the same size it was previously despite the size of the model db.

Still no solution. BTW, running SQL 2000 SP3.

Eric Whitney SSC-Addicted Points: 428 More actions · Answer 3

For those interested, another forum generated this KB article that I had missed.

307487 - HOW TO: Shrink the Tempdb Database in SQL Server

This takes care of it.

Eric

cchitanu SSC-Addicted Points: 450 More actions · Answer 4

One of the most important and really interesting details about tempdb is that it is rebuilt every time SQL Server is restarted, which means if you stop the SQL Server and then start it again, the server will clear out the files from tempdb that currently exist. It will use the model to create the tempdb appropriately and will expand or configure it appropriately to specify the configuration that the database administrator (DBA) has set already.

Let's say we are usually allocating the initial status after we install SQL(8 MB of data file and 0.5 MB of log file) and a DBA decide to increase the data file to 200 MB and the log file to 50 MB. This will be the DBA set status.

Then some operations come in and they start using tempdb. By default, the database is set to autogrow and it may grow and grow and grow and let's say it reaches a maximum size of 500 MB data file and 140 MB log file. What happens after SQL is restarted? This is something that most people neglect here. I've heard many different opinions from customers. Some say that tempdb will get back into the very initial status. Some say it will be the same as the model.

It is actually kind of confusing, but this is what will happen after restarting the server. tempdb will get back and will reconfigure to the same configuration as the one the DBA had set there. It will not reflect the autogrow that happened during the normal working operations. It will go back to this DBA set status

Check this link for more info:

http://support.microsoft.com/default.aspx?kbid=834846

Cristian

Eric Whitney SSC-Addicted Points: 428 More actions · Answer 5

Christian, your assessment is correct. For those interested, the characteristics that Christian refers to regarding the "DBA set status" can be found in the sysaltfiles table.

Eric