locked
Multiple data and log files RRS feed

  • Question

  • User34108037 posted

    While checking the data and log files for the tempdb in one of the production server , it has 8 mdf files and one log files and each data file set auto growth to 1024 MB and initial size as 25GB and unlimited growth.

    Log file initial size is 10 GB and auto growth by 1024 MB and unlimited growth.

    I dont understand clearly, if we have one mdf file and its auto growth set to unlimited then why should we have other mdf files? 

    What is initial size? we are assigning some size to the file. when it reaches to the size then it starts auto growth by given size and it will grow unlimited size. if so then why should we have other mdf files with unlimited growth?

    Suresh.

    Wednesday, March 2, 2016 8:22 AM

Answers

All replies

  • User-219423983 posted

    Hi ksureshh_pk,

    While checking the data and log files for the tempdb in one of the production server , it has 8 mdf files and one log files and each data file set auto growth to 1024 MB and initial size as 25GB and unlimited growth.

    I dont understand clearly, if we have one mdf file and its auto growth set to unlimited then why should we have other mdf files? 

    When you install the SQL SERVER, the would create a tempdb database with an 8MB data file and a 1MB transaction log file by default. For this settings, you could have a look at the following image. In your case, it’s really strange that there are 8 mdf files. I think you’d better through the following image to check which one is used to tempdb.

    Note: When you restart your SQL Server instance, tempdb is re-created (files will be reused if they already exist) and sized to the value specified in the database properties, which as you’ve just seen is only 8MB for the data file and 1MB for the log file by default.

    when it reaches to the size then it starts auto growth by given size and it will grow unlimited size. if so then why should we have other mdf files with unlimited growth?

    Maybe the other 7 mdb files are used to other database, not for the tempdb. You’d better check your SQL Server Studio to check each database and find out their mdb file location.

    Besides, the following articles provide some suggestion about tempdb initial size and autogrowth, I suggest you’d better read them carefully.

    https://technet.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-initial-sizing-w01/#.Vtaz3v5f100

    Best Regards,

    Weibo Zhang

    Wednesday, March 2, 2016 9:54 AM
  • User77042963 posted

    Using multiple tempdb data files is one of the best practice settings. 

    You can check this:

    http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-multiple-files-w01/#.Vtb5X_krJhE

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 2:33 PM