Defrag DISK where MDF and LDF files are

Question

Defrag DISK where MDF and LDF files are

SQLAssAS

SSCertifiable

Points: 7246
More actions
October 23, 2013 at 2:52 am

#281690

What is the best practices around this? Should SQL Server be stopped to allow the disk to fully defrag? What are the implications \ potential risks with defragging whilst SQL is online?

Viewing 7 posts - 1 through 6 (of 6 total)

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

BrainDonor SSCoach Points: 19252 More actions · Answer 1

Stop the service before you defrag a hard-drive. Any errors during the defrag and you'll have a corrupt database - it isn't worth the risk.

Make sure you have a backup of the DB prior to this operation too.

Steve Hall
Linkedin
Blog Site

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Make sure that SQL is stopped for the entire duration (or if it's just one database out of many, that the database is offline)

If you have a tool that claims to be able to do online defrags, see previous point

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Do have RAID setup ? if nto then think about that too .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

SQLAssAS

SSCertifiable

Points: 7246

October 23, 2013 at 7:35 am

#1660925

Thanks guys

EdVassie SSC Guru Points: 60445 More actions · Answer 5

You need to know how the drive letters that Windows sees are mapped to your underlying storage. If your storage uses thin provisioning then you are probably wasting time doing a Windows-level defrag. Even with thick provisioning you need to know how disk tracks are assigned within the storage subsystem, as many vendors use a common track allocation algorithm for both models.

With thin provisioning, the typical way space is allocated within a SAN os NAS device is on a track by track basis. When you want to write data to a disk track, a new one is taken out of the free space pool and used. When you want to update a track, a new track is taken out of the free space pool and used, with the old track put into garbage collection and eventually returned to the free space pool.

Therfore at physical disk level your data can be completely fragmented regardles of what is seen by Windows. In this situation the only advantage of a Windows defragmentation is reducing the chain of pointers used by NTFS to map space at the Windows level - this may save a few CPU cycles but would have no impact on IO speed.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

SQLSteve (10/23/2013)
What is the best practices around this? Should SQL Server be stopped to allow the disk to fully defrag? What are the implications \ potential risks with defragging whilst SQL is online?

DISKKEEPER will do all of this in the background for you with the servers ONLINE. I've not used it in the last 3 years but it was never a problem before and it took care of things auto-magically without any intervention required from me.

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