Unable to defrag a disk volume with an instance of MSSQL running

garychu
garychu used Ask the Experts™
on
I have tried unsuccessfully to defrag the hard disk on which there is an instance of a MSSQL 2000 SP3 server.

Operating system is Windows 2003 Std Server SP2
MSSQL is installed on a separate disk volume from the OS. The MSSQL volume is a bunch of 4x72GB SCSI hard disks configured as RAID 5.

Repeated attempts at defragging (using the OS defrag) returns message that the <database>.ldb file could not be defragged.

The defrag attempts were always with the MSSQLSERVER and SQLSERVERAGENT services first stopped.Additionally, I have tried taking offline the <database> before-hand. And even detaching the <database>. But each time, the defrag fails.

what could be the reason?

Any assistance to resolve this issue would be appreciated. Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Check you have enough free disk space must remain at least a third of available space. It is possible to use a commercial defragmenter? Built-in defragmenter in my opinion does not work with system areas NTFS, perhaps in this problem.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Repeated attempts at defragging (using the OS defrag) returns message that the <database>.ldb file could not be defragged.

SQL Server Data(*.mdf) and Log(*.ldf) files can't be formatted using Windows De-fragmenter or any other de-fragmenter .

>>  I have tried taking offline the <database> before-hand. And even detaching the <database>. But each time, the defrag fails.

No need to Defrag those files if you perform Index Rebuild activities periodically..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> can't be formatted using Windows De-fragmenter or any other de-fragmenter

Typo, its should be

"can't be defragmented using Windows De-fragmenter or any other de-fragmenter"
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

DavidPresident
Top Expert 2010

Commented:
First, make 100% that SQL Server and ALL the services are really shut down.  Perhaps you have another service that uses one of the files, or an executable that has one of them open that is still running.  You can shut down the database, but that won't automatically close any program that already opened a file.

My bet is that you missed something.    Try to disable all services and reboot, then defrag.   Also, download the contig.exe program from the microsoft system internals website.  Contig does a better job than defrag, and you can point it to a single file.  System internals is now owned by MSFT, and it uses the API call, so this is a safe program.  They also have a utility that can defrag the "unmovable" files on a reboot.  

Commented:
Hello there,

First of all i think there must other application accessing the service. If not don't worry.
Even though you have taken the database offline, the service is still running. Don't worry about degrag log file which can be done by other methods, one of which is using DBCC SHRINKFILE.
Just to defrag the disk, please stop the service from control panel->administrative tools-Services->MS SQL server and other services such as SQL server agent etc.
Also make sure of the disk space available, which I think is not an issue.

Please do let me know your progress

Cheers

Author

Commented:
Thanks, experts.
Pardon my hesitation. But turning off ALL services (presumably User & System) seems a bit drastic.
Index rebuild is done regularly. But application vendor support insists that this is not adequate as it is only on a logical level. Disk defragmentation is still needed.
If I go along with them, does this mean that the only way to do it is to use something like Diskeeper or equivalent?
DavidPresident
Top Expert 2010

Commented:
Not all services, just the services that are using the sql file in question.   The message means that the file(s) are in use, so you must have missed something.  Try downloading process monitor to see what program(s) are using the database file(s).

http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> But turning off ALL services (presumably User & System) seems a bit drastic. Index rebuild is done regularly

As I mentioned, you need not defragment MDF and LDF Files and you can't do it using windows defragmenter..
If you try to do so with any other tools, then your Indexes would be heavily fragmented and hence need to Rebuild index at database level.

Not sure whether you are following my comments from the beginning..
DavidPresident
Top Expert 2010

Commented:
".. you need not defragment MDF & LDF files".

Why do you say that, when MSFT employees say otherwise?   (Example, link below written by the moderator at MSFT's SQL Server blog site states otherwise)?
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/eeba7951-aa8c-4143-a47d-0d97eac32ac8

Another possibility .. do you have any antivirus software installed?  Maybe that is interfering.

Finally, here is another MSDN blog that has several articles about defragging, and more importantly, measuring the effects of fragmentation, and how to tune.  http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/index+fragmentation+series/default.aspx

Commented:
Hi again,

I have faced this problem before. The only solution i found out is by taking the database offline (if anyone is online with any sort of transactions). Like "dlethe" said, please stop antivirus. Sometimes eventhough you stop the application, the service will still be running. Please stop the service for the antivirus from control panel and at last only stop the SQL service and nothing else. You must be able to defrag without any problem.
Like I said before the only way you can defrag the SQL server files is by shrinking.
If possible rebuild the indexes.

Bye for now and let me know your progress

Cheers
Top Expert 2012

Commented:
dlethe,
>>Why do you say that, when MSFT employees say otherwise? <<
Then I suggest you re-read the thread. Jonathon is not saying you should do a Windows Defrag, he is stating that you can do a Windows Defrag.  In fact he goes further and states that you should not have to do a Windows Defrag. "The primary way that external fragmentation is introduced to the SQL Server is through consistent growing and shrinking of the database files, which should not be occuring."
DavidPresident
Top Expert 2010

Commented:
Pesky words ... "should not have to", "shouldn't", "need not", all mean different things.  

I defer to your greater expertise in SQL server, as I am a storage/RAID architect, but when confronted with this ambiguity in the MSFT sites, as well as your response, and follow-ups, then I hope you understand my confusion.

I had always responded with, "it won't hurt, but in vast majority of situations the benefit (of defragmentation index files) of will be nominal, so you should spend your time elsewhere looking for optimization."  Is this still the best response for people, or do you suggest a better response.  I didn't editorialize the reason the author wanted to defragment other than take minor exception with your response as i interpreted as you should not defragment index files.

Author

Commented:
I am keeping this question for just a little while longer.
This will allow me to take on board as much advice from as many experts as possible before I draw a conclusion.
Thanks in the meantime.

Author

Commented:
I had no idea that defragging a volume which holds an MSSQL database could generate such a debate.
I will certainly do some more researching to better understanding the issues involved.
Meanwhile, I think I have a practical answer to my immediate problem.
Thanks, experts, for the contribution to my learning.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial