Single files defragment for SQL Server

Question

Single files defragment for SQL Server

Garrick-249518

Ten Centuries

Points: 1346
More actions
July 24, 2006 at 7:06 pm

#61678

Hi,
Saw this free file defragment tool on
http://www.sysinternals.com/Utilities/Contig.html
I am wondering is anybody ever use this tool or (similar in nature) to perform single file defragment for SQL Data files and log files?
Is there any problem if one only defrag SQL Server data files and log files instead of defrag the entire disk?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if the file is in use, contig will skip the file, so yuo'd have to stop the server to defrag the mdf/ldf files;

i have it scheduled on my dev machine to run daily, since i never turn it off; i may be biased but i think it makes a difference with performance.

it runs from a command prompt similar to dir or copy, so you can make it defrag specific file extensions, ie contig c:\*.mdf /s

that's start at the root of c:\, defrag only mdf extensions, /s= search all subdirefctories

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 2

Lowell,

Thanks for your input.

i have it scheduled on my dev machine to run daily, since i never turn it off; i may be biased but i think it makes a difference with performance.

I don't quite get your message right. Do you mean if you don't stop the SQL service and run the defrag tool for mdf/ldf. You will experiece performance hit on SQL Server duirng defrag period?

Or do you mean the other way round, performance increase after defrag?

Lowell SSC Guru Points: 323518 More actions · Answer 3

well my dev machine is just that...development, sample data, and if i take the service down to defrag, there's no real impact. , so i have a scheduled task of a bat file that turns off the sql service (net stop ), runs contig.exe on my c drive, then restarts the service, then moves on to my other disk.

our production machine is also scheduled for defragging, but just once a week.

if you run contig while sql server is running, because the mdf /ldf files are locked by the sql server process, it skips the file and moves on to the next.

try it by finding specific files and go to a command prompt:

contig.exe "c:\Program Files\*.mdf /s"

you see it find the files in a hurry, but it'll skip most and only defrag any detatched mdf files.

I can't prove it with statistics, but my dev machine seems to perform faster with constant defragging...but i'm probably beating the hell out of the harddrives every day; a harddrive failure wouldn't suprise me

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!