May 4, 2012 at 10:19 am
Here is a situation that seems to be answered frequently, but also I have seen several conflicting answers to the similar questions. Since this is DB related and Windows related I figured this is a good forum to start….
Q1). The initial question is: can the default windows Defrag.exe on a Windows 2003 Server (or greater OS (run from command prompt) defrag a MS SQL DB file (MDF, NDF, LDF) while SQL is still active (services running)?
I know this seems pretty straight forward age old question, but I am going to point you to initial links that state the very opposite opinions and it also seems to vary based on various operating systems. For example everyone knows Brad Mcgehee from the DBA community and his 2011 article states the following, “The problem is, is that the defrag.exe program will only defrag closed files, not open files, such as any SQL Server MDF, NDF, or LDF files that are in production”. To read more: http://www.bradmcgehee.com/2011/06/do-you-ever-physically-defragment-your-sql-server-mdf-ldf-files/#ixzz1tubRueOC. However on another SQL server central forum post from SSCrazy, in 2009, states “Windows Server 2003 supports defragmentation of open files”. To read more: http://www.sqlservercentral.com/Forums/Topic650537-146-1.aspx.
Q2). So, which is the correct answer?
To complicate things even further, it appears that in Windows 2008, when running the Defrag.exe there is a new switch (/W or –w), that will or will not include files larger than 64 MB to be included in the defragmentation process. The command prompt defrag.exe states a possible limitation that “On NTFS volumes, file fragments larger than 64 MB are not included in the fragmentation statistics”.
Q3). So, this begs the question, does a file larger that 64 MB not get defragmented because it is not included in the statistics?
Q4). Most SQL DB data files are much larger in size so, then can we expect a MDF, NDF, LDF really be defragmented at all?
Q5). And if it can be defragged, which switch option does the GUI version of Defrag.exe use or should all SQL DBA’s be using the command prompt instead to get the expected results?
What are your opinions, jeers, thoughts, answeres? (Q6 an beyond)
😀
May 4, 2012 at 10:29 am
I think the confusion here is that the Windows API allows files to be defragmented online since Win2k3, but you'd need a 3rd party tool that supported it. The built in defrag tool (I'm 99% sure) does not touch in use files at all.
To be honest, I don't worry about it so much these days - most of my DB's are on large complicated SANs and I have to trust that this is being managed by the storage team at that level. Even when on local disks, as long as everything's sized well, has chunky growth settings etc. it's not much of a problem.
Obviously internal database fragmentation is a completely different matter.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply