how to allocate the File sizes (Disk Partition Alignment for tempdb)

• Database admin(DBA)

  SSCertifiable

  Points: 5373

  More actions

  June 29, 2015 at 1:24 pm

  #318634

  I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.

  how to allocatte the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.

  Can you explain me what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?

  what is 64 KB cluster has 128 sectors?

  tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB

  Please suggest me.

  Thanks in advance!!

  Thanks & Regards,

  DBA

  SQL server DBA

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  June 29, 2015 at 7:17 pm

  #1808957

  Database admin(DBA) (6/29/2015)

  ---

  I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.

  how to allocatte the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.

  Can you explain me what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?

  what is 64 KB cluster has 128 sectors?

  tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB

  Please suggest me.

  Thanks in advance!!

  Thanks & Regards,

  DBA

  You've got 3 separate tags on your post that claim you're a DBA. You should know this off the top of your head (or at least how to Google for such a thing) or you should humble down a bit.

  Here's a start...

  https://www.google.com/?gws_rd=ssl#q=number+of+tempdb+files+kimberly++tripp

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

• Perry Whittle

  SSC Guru

  Points: 233915

  More actions

  June 30, 2015 at 1:35 am

  #1808989

  Database admin(DBA) (6/29/2015)

  ---

  how many log files needs to be created

  You only need 1 transaction log, size it appropriately for your system

  Database admin(DBA) (6/29/2015)

  ---

  Can you explain me what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?

  Ntfs allocation size is set when you format the volume, you dont divide the files this is done for you when you create the file.

  Database admin(DBA) (6/29/2015)

  ---

  what is 64 KB cluster

  see above

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• DeWayne_McCallie

  Hall of Fame

  Points: 3617

  More actions

  July 2, 2015 at 10:36 am

  #1809991

  When formatting a disk in windows the default blocksize is 4K. You must choose a different blocksize (64K) and format the disk for best performance for SQL. A format will loose anything on the disk so you need to move all of your folders/files from the disk to another disk, format the disk then move the folders/files back. Also, if you just move the files and create new folders after reformatting the drive you will have to add permissions to the folders to get SQL to start.

  Here is the process assuming that you have a drive named G: where tempdb is located:

  1. Stop SQL

  2. Move the folders/files from G: to another disk

  3. Format G: and specify 64K as the blocksize

  4. Move the files back to G:

  5. Start SQL

  For TempDB you want multiple DATA files. You only need one TempDB LOG file.

  Hope this helps!