Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Format Disk for SQL Server

Managing large SQL Server inventories requires standardisation. Do your research , create the scripts , and apply.

Occasionally, custom changes are required . This should be after careful testing.

One advantage of standardising is  for  Disaster Recovery. The rebuild is in the scripts.

If full server rebuilds are required – disk format for SQL Server performance is one area to focus on. Not formatting the disks in an optimal way – can lead to negative feedback from end users.

Disk format details is one of those areas that can easily be overlooked when diagnosing a SQL Server performance problem.

This is an example of  a procedure for  adding and formatting a new drive , in Windows 2003 32 bit  for a base build for SQL Server 2005 .  

Once you’ve standardised the approach , create a document or script  and add to script repository

 

C:\Users\Administrator>diskpart 
Microsoft DiskPart version 5.2.3790.3959
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: Server1

DISKPART> 

DISKPART> list disk


Disk ###  Status      Size     Free     Dyn  Gpt
--------  ----------  -------  -------  ---  ---
Disk 0    Online        67 GB      0 B
Disk 1    Online        20 GB      0 B
Disk 2    Online      1020 MB      0 B
Disk 3    Online      1020 MB      0 B
Disk 4    Online      1020 MB      0 B
Disk 5    Online       200 GB      0 B
Disk 6    Online       100 GB      0 B
Disk 7    Online        50 GB      0 B
Disk 8    Online       200 GB      0 B


DISKPART>select disk 5 

Disk 5 is now the selected disk.

DISKPART>list partition

Partition ###  Type              Size     Offset
-------------  ----------------  -------  -------
Partition 1    Primary            200 GB    32 KB



--To find the file allocation unit size 
fsutil fsinfo ntfsinfo i:

---Currently
NTFS Volume Serial Number :       0x1828b55d28b53a98
Version :                         3.1
Number Sectors :                  0x00000000063fbbbe
Total Clusters :                  0x0000000000c7f777
Free Clusters  :                  0x000000000061e7b2
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000000000c000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x000000000063fbbb
Mft Zone Start :                  0x00000000000c0000
Mft Zone End   :                  0x000000000024ff00


--Changing to 


NTFS Volume Serial Number :       0xcc82786d82785dc0
Version :                         3.1
Number Sectors :                  0x0000000018ffeabc
Total Clusters :                  0x000000000031ffd5
Free Clusters  :                  0x0000000000181bc7
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000050000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x00000000000c7ff5
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x0000000000070000

I deleted the partition and recreated the partition with the following steps. As this reformats the drive , consider moving all data onto another drive first

 

diskpart
   rescan
   list disk (figure out the new disk #)
   select disk X
   create partition primary align=1024
   select partition 1
   assign letter=I:
 
format /FS:NTFS /Q /A:64K I:\
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.