TempDB File Size Per TempDB FIle

  • SQLDude

    Old Hand

    Points: 308

    I'm currently setting up multiple SQL Server 2017 instances using a configuration file. I have the TempDB separated into the recommended 8 files and have the TempDB disk is 100GB in size. My question is, do I need to go an alter each of the 8 files or do I just use the ALTER DATABASE TempDB... for the original file? I would like to also turn off autogrowth. Thanks in advance!

  • SQLDude

    Old Hand

    Points: 308

    I've answered my own question. I do need to change the *.mdf file as well as the *.ndf files so that they write/fill proportionally. I use the following T-SQL to perform the task.


    ALTER DATABASE tempdb     
    MODIFY FILE (Name=<filename.mdf>,
    Size = 12250);
    GO
    ALTER DATABASE tempdb    
    MODIFY FILE (Name=<filename.ndf>,
    Size = 12250);
    GO

  • Jeff Moden

    SSC Guru

    Points: 994284

    trent.adams0201 - Wednesday, March 27, 2019 11:33 AM

    I've answered my own question. I do need to change the *.mdf file as well as the *.ndf files so that they write/fill proportionally. I use the following T-SQL to perform the task.


    ALTER DATABASE tempdb     
    MODIFY FILE (Name=<filename.mdf>,
    Size = 12250);
    GO
    ALTER DATABASE tempdb    
    MODIFY FILE (Name=<filename.ndf>,
    Size = 12250);
    GO

    That only leaves you 2GB for the LDF... could be a form of "Death by SQL".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • SQLDude

    Old Hand

    Points: 308

    @Jeff Moden

    Jeff Moden wrote:

    trent.adams0201 - Wednesday, March 27, 2019 11:33 AM

    I've answered my own question. I do need to change the *.mdf file as well as the *.ndf files so that they write/fill proportionally. I use the following T-SQL to perform the task.

    <span id="if_insertedNode_1553707938884">

    ALTER DATABASE tempdb

    MODIFY FILE (Name=<filename.mdf>,

    Size = 12250);

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (Name=<filename.ndf>,

    Size = 12250);

    GO</span>

    That only leaves you 2GB for the LDF... could be a form of "Death by SQL".

     

    Thanks Jeff, I've made changes accordingly.

  • Jeff Moden

    SSC Guru

    Points: 994284

    Thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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