TempDB – SQL Services restart | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

TempDB – SQL Services restart

I knew that when you restart the SQL Services, tempDB will be recreated, the new tempDB will be copied from the current model database. But I don’t know the details. Such as today when I restarted the SQL Services on a 2000 EE SP4 machine, I checked the tempDB, it was recreated, but not totally identical to the model DB, at least data and log file size are not identical, the model db data file only 2MB, but new tempDB data file size is 2GB. Anybody here know this issue can explain?
——————
Bug explorer/finder/seeker/locator
——————
Once the tempdb is created upon start of the SQL Server service, it will automatically grow as needed. If you find that your tempdb always grows to minimum size, say 500MB, one option is to specify that the tempdb always start at a minimum size of 500MB so that the growth overhead will occur shortly after the server is started, and not occur during busy production times. ——————————–
Brad M. McGehee, SQL Server MVP
http://www.sqlbrad.com
Model is used as a template for user database and not for system databases. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I belive model is used as template for tempdb also but size might be taken from sysaltfiles…. It can be done simple test…
Create a user in model db with dbo rights and restart the sql services… new user will show up in tempdb with dbo rights…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

No, not for the sizes. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
check whether there is any start up job scheduled for any maintenance,creation of any global temp tables etc. or any kind of job which uses tempdb heavily and it is scheduled at the startup. Madhu
Don’t think there is any start up job which can expand the tempdb that quickly
quote:Originally posted by madhuottapalam check whether there is any start up job scheduled for any maintenance,creation of any global temp tables etc. or any kind of job which uses tempdb heavily and it is scheduled at the startup. Madhu

——————
Bug explorer/finder/seeker/locator
——————
If you are performing the regular startup of SQL Server then best to have an adhoc scheduled job to increase the TEMPDB to required size. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I don’t it needs a job to increase the size every time sql starts…
One time increase the tempdb manually using ALTER DATABASE command as needed and next sql restart will automatically creates the tempdb the same size as before sql restart.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

That depends on the version of SQL you are using Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
quote:Originally posted by MohammedU I don’t it needs a job to increase the size every time sql starts…
One time increase the tempdb manually using ALTER DATABASE command as needed and next sql restart will automatically creates the tempdb the same size as before sql restart.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

My comments applies to SQL SERVER 2000 and 2005… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

]]>