Defragmenting my SQL Server – Best order/practices?

• rhunt

  SSCertifiable

  Points: 5868

  More actions

  January 12, 2005 at 10:43 am

  #164270

  I have a server with DB’s that have been autogrowing for several years – constantly fragmenting the disks and placing different portions of MDF’s and LDF’s in different areas of the drives.

   

  I now have a growth strategy in place for all 30 DB’s and I would like to defragment everything as much as possible – however, I am undecided on the order of things.

   

  For instance, should it be: (in steps)

   

  1)      Set new annual size of each DB

  2)      Reindex all indexes per DB for fill factor of 90%

  3)      Shut down SQL Services and run disk defragmenter

  4)      Additional suggestions…

   

  OR

  1)      Set new annual size of each DB

  2)      Shut down SQL Services and run disk defragmenter

  3)      Reindex all indexes per DB for fill factor of 90%

  4)      Additional suggestions…

   

  Thanks in advance.

   

  Ryan Hunt

• Jared Metzger

  Right there with Babe

  Points: 766

  More actions

  January 12, 2005 at 12:08 pm

  #537101

  My 2 cents:

  1) Reindex

  2) Set new size

  3)Disk defrag

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 12, 2005 at 12:58 pm

  #537120

  See, if this helps:

  http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

   

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• rhunt

  SSCertifiable

  Points: 5868

  More actions

  January 13, 2005 at 12:55 pm

  #537363

  Thanks Frank.  That article was very helpful.

  I will be:

  1) Setting size for the year

  2) Running disk defrag (with SQL services off I assume - the article didn't stipulate)

  3)  Running DBCC DBREINDEX in the middle of the night.

  Thanks. RH