SQL Server Performance

TempDB - SQL Services restart

Discussion in 'Getting Started' started by xiebo2010cx, Apr 27, 2007.

  1. xiebo2010cx Member

    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
    ------------------
  2. bradmcgehee New Member

    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
  3. satya Moderator

    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.
  4. MohammedU New Member

    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.
  5. satya Moderator

    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.
  6. madhuottapalam New Member

    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
  7. xiebo2010cx Member

    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
    ------------------
  8. satya Moderator

    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.
  9. MohammedU New Member

    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.
  10. satya Moderator

    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.
  11. MohammedU New Member

    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.

Share This Page