July 7, 2008 at 10:08 am
Hey all,
I'm curious to as what the recommended practices are for external fragmentation on data/log file drives for SQL Server are. Most of my servers appear to be sitting around 40-60% fragmented for both data and log drives. I'm assuming the majority of this is caused by shrink/growth.
Is it ok to defrag data and log files while SQL Server is running or does it have to be taken offline?
Any information would be most appreciated.
Thanks
July 7, 2008 at 10:21 am
My kids always tell me that they won't spill grape juice on the couch. When I catch them drinking over there, they say that they haven't spilled it, so it's OK. When it happens (and it's a when, not an IF with kids), it's a very, very big deal.
Take the files offline to defragment them. Everyone says that things will be fine, but if they're not, I don't want blocks on my database to be out of place.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 7, 2008 at 10:25 am
Great point ... That is the path that I would want to take as well, but obviously not always the easiest of tasks.
I've heard that some software out there claim to not cause any issues with keeping SQL online ... such as Disk Keeper. Again, while I personally would prefer to take the server offline for this type of maintenance, I need to find out if there are any solutions out there that would allow us to keep it online. Anyone have any experience in this matter?
July 7, 2008 at 10:29 am
I have not found any tools that don't cause problems on databases of significant size (10gb or more) that have activity during the process.
July 7, 2008 at 10:29 am
I have used DiskKeeper for this purpose and found it to work quite well. As we all know, in production systems it is often very difficult, if not impossible to take a system off-line to address this issue. So tools such as DiskKeeper are critical in resolving the issue.
July 7, 2008 at 11:25 am
Michael Earl (7/7/2008)
I have not found any tools that don't cause problems on databases of significant size (10gb or more) that have activity during the process.
I have successfully used DiskKeeper successfully at my previous employer. I quoted Michael here because he notes a 10GB threshold. All 95 of the databases I had running were just under this 10GB threshold. Don't know if that has anything to do with it or not.
Doesn't DiskKeeper have some sort of guarantee that they won't cause problems? Seems like they should since they seem to be the only one out there that says they can defrag the database with it online.
July 7, 2008 at 12:00 pm
Open file defragmentation is only supported with Windows Server 2003 or higher. It is not possible with Windows Server 2000 or earlier.
You can use the built-in windows defragmentation utility in Windows Server 2003, but you have to run it manually. Any of the commercially available defragmentation utilities will be able to do online defragmentation; they all use the same Windows APIs for this.
We have run defragmentation online of with databases larger than 100 GB on volumes with 700+ GB of database files using Diskeeper. It took a long time to complete, but there was no problem with it.
July 7, 2008 at 12:20 pm
I have tested Diskeeper and found that it could leave corruption in tables with BLOB fields on large, heavy-transaction databases.
I was working on a document management system and it would sometimes leave orphaned BLOB's requiring DBCC to repair with data loss. That was where I bailed out on continuing testing with it.
This was a couple of years ago at a previous company, so I cannot give you any information about the current Diskeeper products. We had a test system holding about 100gb of data in which I could run about 50 transactions per second during the defrag process - a relatively heavy test that may be more than you need.
I don't feel comfortable listing out the products I tested during that process, but the list included the top players. Most of them had similar issues. My only advice is if you go this route that you test on a system where you can reproduce the size and processing load on your system while trying to defrag. I was very surprised that some of the products did not live up to their marketing.
July 7, 2008 at 2:42 pm
I am surprised that no one has mentioned this, but there is another option. It does require a downtime, but may not take as long as using a defrag tool.
Simply put, backing up the database and restoring the database will recreate the database files contiguously. This may not take as long due to instant initialization and is, at least, worth looking into.
Either way, I personally would not recommend performing a defrag on a production system without taking the system offline.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2008 at 2:49 pm
If your server use SAN storage, you may not do the defrag. The defrag may cause issues on other servers.
July 7, 2008 at 11:12 pm
I think you're gambling. What works well for Michael or anyone else might not work for you.
Take the downtime, defragment in stages if necessary.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 9, 2008 at 12:15 pm
maybe 'Contig' from former Sysinternals is a good option.
http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply