Post reply

Temp DB Best Practice

  • July 14, 2009 at 4:05 pm

    #139815

    Hi,

    We are building a new server planning migration from sql 2005 to sql 2008 on windows 2008 server.

    we have an 16 processor,64 GB RAM,.Each Database has seperate SAN of 2tb

    We are planning to set up the TEMP DB on a seperate disk which is of 300GB and RAID 10 .

    As a best practice we are planning to stripe the Data file to 16 files i,e one .mdf and 15 ndf's.

    our largest database is around 1TB at present (Data coming in).With huge transaction processing and Batch processes going on.

    we are struck up with Defining the optimum sizes for the LOG and Data Files.

    Are there any particular statistics to set the Data and log file sizes.Like Log file should be 1/2 the size of Data file.

    Tried searching online lot of articles but cannot find any particular statistics.

    Any help is appreciated.

    Thanks in Advance!!!

  • July 15, 2009 at 12:05 am

    #1024821

    Usually Log file is 20-25% of your datafile. But still it depends on the application you are running, frequency of T-log backup, using logspace at the time of maintenance etc.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • July 15, 2009 at 5:22 pm

    #1025417

    @free_mascot

    Thanks for the reply...The size of log file should be 20-25% of the whole Datafile or the striped ndf's.

  • July 15, 2009 at 9:43 pm

    #1025454

    free_mascot (7/15/2009)

    Usually Log file is 20-25% of your datafile. But still it depends on the application you are running, frequency of T-log backup, using logspace at the time of maintenance etc.

    HTH

    Ummmm.... maybe for something else, but not for TempDB. You don't even take backups of TempDB.

    --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)
    Intro to Tally Tables and Functions

  • July 15, 2009 at 9:46 pm

    #1025457

    aravind_0404 (7/15/2009)

    @free_mascot

    Thanks for the reply...The size of log file should be 20-25% of the whole Datafile or the striped ndf's.

    For TempDB, I'd say "neither". If you're migrating databases as you say you are, you should already have a good idea as to how big these things should be for the given system. Migration shouldn't change those requirements by much.

    --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)
    Intro to Tally Tables and Functions

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

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