How to make tempdb use > 1 datafile

  • If a server keeps running out of space for the tempdb one option seems to be, move tempdb to a drive with more space. 😀

    However, what's wrong with just adding another datafile (tempdb_data2.ndf) on a secondary drive?

    Will tempdb use this file if the primary (mdf) is full?

    Should I limit the mdf file to say 2Gb thus forcing SQL to use the ndf once this limit is reached?

    Will it all go horribly wrong? :crazy:

  • I usually run tempdb with files = number of proc cores/2. Once I decide what the usual growth is I fix the file sizes so that tempdb does not normally ever grow.

    as to where you put the files - your choice, I prefer dedicated drives, but make sure you don't use your slowest or most io restrictive drive

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • FNS (12/30/2008)


    If a server keeps running out of space for the tempdb one option seems to be, move tempdb to a drive with more space. 😀

    However, what's wrong with just adding another datafile (tempdb_data2.ndf) on a secondary drive?

    Will tempdb use this file if the primary (mdf) is full?

    Should I limit the mdf file to say 2Gb thus forcing SQL to use the ndf once this limit is reached?

    Will it all go horribly wrong? :crazy:

    It won't go horribly wrong, but it is not a recommended configuration. If you have multiple CPU's and your workload causes bitmap contention from rapidly creating/dropping temp tables then adding multiple files can be helpful, see Paul Randals blog on the subject of multiple database files:

    http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

    By adding a second file to a different drive only, your files are not going to be sized the same when the one drive runs out of space or the file is filled. The general recommendation is that the files be sized the same since SQL Server uses a proportional fill algorithm to stripe the data across the files based on file size and free space in the file.

    Moving tempdb is a really easy process and generally only requires a quick restart of the SQL Services. If you are having space issues on your C Drive, then you should probably move tempdb to a different drive altogether. You likely have fragmentated free space and ar spreading the file across the fragmentated space since it is created at SQL Startup.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • I am about to move our tempdb soon as well (due to unexpected crazy growth that fills up C drive)

    move tempdb code

    USE [master]

    GO

    ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\TempDB\tempdb.mdf')

    GO

    ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\TempDB\templog.ldf')

    GO

    select * from tempdb.dbo.sysfiles

    add multiple files (1 per CPU) sample

    -- 5GB file, grows at 100MB

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'T:\MSSQL\DATA\tempdb2.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb3', FILENAME = N'T:\MSSQL\DATA\tempdb3.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb4', FILENAME = N'T:\MSSQL\DATA\tempdb4.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb5', FILENAME = N'T:\MSSQL\DATA\tempdb5.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb6', FILENAME = N'T:\MSSQL\DATA\tempdb6.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb7', FILENAME = N'T:\MSSQL\DATA\tempdb7.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb8', FILENAME = N'T:\MSSQL\DATA\tempdb8.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

    GO

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • FNS (12/30/2008)


    If a server keeps running out of space for the tempdb one option seems to be, move tempdb to a drive with more space. 😀

    However, what's wrong with just adding another datafile (tempdb_data2.ndf) on a secondary drive?

    Will tempdb use this file if the primary (mdf) is full?

    Should I limit the mdf file to say 2Gb thus forcing SQL to use the ndf once this limit is reached?

    Will it all go horribly wrong? :crazy:

    Yes, u can use set size of perticular database files (.mdf,.ndf,.ldf) by altering/creating database.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Hi Jerry

    I am looking at your code for tempdb files

    I am new to sqlserver 2005

    Not up to speed on TEMPDB files yet

    i understand the creation of the multiple tempdb#.mdf files, but what about the templog.ldf file ?

    is there one per tempddb file you created of just one for the entire set ?

    Thanks

    Jim

  • JC (6/29/2009)


    Hi Jerry

    I am looking at your code for tempdb files

    I am new to sqlserver 2005

    Not up to speed on TEMPDB files yet

    i understand the creation of the multiple tempdb#.mdf files, but what about the templog.ldf file ?

    is there one per tempddb file you created of just one for the entire set ?

    Thanks

    Jim

    You only need one log file per database - and having additional files does not improve performance at all. Log files are written to sequentially so having another log file does not make sense.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hi Jerry

    another question

    are those tempdb files 2 thru 8 suppose to be .ndf files instead of .mdf files ??

    Thanks

    Jim

  • Thanks Jeffery

    can you answer that last question for me i just posted by any chance

    Thanks

    Jim

  • JC (6/29/2009)


    Hi Jerry

    another question

    are those tempdb files 2 thru 8 suppose to be .ndf files instead of .mdf files ??

    Thanks

    Jim

    The generally accepted naming standard is to name secondary data files using .ndf for the extension. However, SQL Server does not care what extension is actually used.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks Jeffrey

    Jim

  • Hi Jerry,

    All the secondary datafiles of Tempdb are located in the same T:\ drive. Do we really require those 8 secondary tempdb files, while the database is created with unrestricted file growth on T:\.

    How does SQL Server utilizes these T:\ .mdf(.ndf) files one by one.

    Thanks

    🙂

  • Please post new questions in a new thread. Thank you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Have you test this configuration on the tempdb?

    How does the files group works if not configure the same size

    Primary mdf data 1 2048 MB

    ndf data 2 2048 MB

    ndf data 3 9600 MB

    when the data fill up the data 1 and data 2. How it works in data 3? Fully uses the 9600 MB or use 2048 only in SQL server 2005\2008?

    Thank

  • JC-3113 (6/29/2009)


    Hi Jerry

    I am looking at your code for tempdb files

    I am new to sqlserver 2005

    Not up to speed on TEMPDB files yet

    i understand the creation of the multiple tempdb#.mdf files, but what about the templog.ldf file ?

    is there one per tempddb file you created of just one for the entire set ?

    Thanks

    Jim

    There's actually a topic in Books Online (the free help-system that comes with SQL Server) on how to move TempDB step by step. My recommendation is to read up on that bad boy.

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

    Change is inevitable... Change for the better is not.


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

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

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