July 8, 2009 at 4:44 am
Hello Gurus.
I have a customer that insists on running a full defragmentation on a production SQL Box. This is not the Index Defrag done via TSql but an OS command line tool (JkDefrag). I know in this is probably not a good idea, but I do not have documentation stating that.
Can anyone point to a good/authoritative article that would present some of the woes of running an OS level defrag on a Production SQL box.
Thanks in advance.
Steve
July 8, 2009 at 5:16 am
Steve, for my shop, we also believe it is a good idea to defrag at the OS level; most machines are scheduled for once a week, and my dev machine i have a scheduled task to run every day; 160 gig drive, 60% free, but because i restore and drop a lot of databases,files and such for conversions, i defrag a lot...i like seeing almost every file is in 1 fragment. the bat file runs in around 15 minutes with contig.exe;
there's a thread here about what tools some folks use:
http://www.sqlservercentral.com/Forums/Topic650537-146-2.aspx
I'd actually pose the opposite question: why is it a good idea NOT to do this on a production box, where performance is important? isn't a little slow performance for a short period during maintenance hours the right thing to do?
I admit our systems are not terabyte installations or anything; largest db is usually around a gig or so, but I'd say it's good to defrag.
Lowell
July 8, 2009 at 5:47 am
Lowell (7/8/2009)
...but because i restore and drop a lot of databases,files and such for conversions...I'd actually pose the opposite question: why is it a good idea NOT to do this on a production box, where performance is important? isn't a little slow performance for a short period during maintenance hours the right thing to do?
Well here's the thing.
While I agree with your "opposite question" at a high level, we do not restore or drop a lot of database files on our production server.
On a Production Sql box, Sql server (Other OS functions aside) should be the only thing doing disk access at any regular interval. This is the case for us.
If SQL is not shut down OS Defrag will simply skip the datafiles and no “defragging” will occur because the files are in use.
Should defragging ever be necessary for the databases shouldn't the TSQL defragmentation command be used?
Steve
July 8, 2009 at 5:53 am
Lowell (7/8/2009)
there's a thread here about what tools some folks use:http://www.sqlservercentral.com/Forums/Topic650537-146-2.aspx
Also there seems to be at least some indication that Contig did nothing for the sytem where the db was in use which lends credibility to the argument in my previous post.
JKDefrag is exactly the tool that I am having difficulties with. On occasion it interfers with my log backup by locking the file causing the log backup to fail. At least that is the diagnosis I am going with right now.
Steve
July 8, 2009 at 6:13 am
ahh, now if the defrag is introducing problems, yeah, I see your point;
Any chance you can reschedule either the defrag or the backup so they don't intersect at the same time?
The last time i ran JkDefrag with all options on my dev machine, it did a great job but took hours; I can see how that can upset any maintenance window.
How often are they running the defrag? every day?
Lowell
July 8, 2009 at 6:18 am
My Log backups are every 10 minutes because I am doing log shipping.
The Defrag schedule is MWF 330am and appears to take around 1.5 hours.
Every MWF without fail about 420-430 I have a log backup failure.
I have disabled log shipping during that window, but I am wondering if this is the best solution.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply