Defragging Disks on SQL Server
-
I apologize if this has been a thread in the past but I did a quick search and didn't find anything.
Does anyone have any best practice recommendations on how to defrag (external OS, not internal SQL defrag) a server with SQL Server running? Is it possible to defragment database files while SQL is still running or will it always prevent the OS from moving the files? Either way, what is the best tool? I've heard that Diskeeper is a good one but it's hard to extrapolate marketing propaganda from actual experience
Thanks!
The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger -
Yes you can defrag the files, however you do have to stop the SQL services while you do so. Afterwards you should defrag the indexes. It would be worthwhile managing your databases manually and giving some headway on each file so that it does not have to grow and thereby cause fragmentation.
SQL Performance has some interesting info on this... http://www.sql-server-performance.com/sql_fragmentation.asp
Diskeeper is a good product for the enterprise, you might want to check out perfectdisk as well, it's a lower cost option.
-
Once your drive is defragged and you have plenty of growth left in each file (as per last poster) then it shoudldn't need defragged very often to keep the MDF and LDF files contiguous.
If you have other files on the drive (backups or apps etc) then the MDF and LDF files will usually stay contiguous unless they have to grow.
For a standalone SQL data drive, then the inbuilt defragger will do once a month I'd reckon (as I do). For other situations, then Diskeeper or PefectDisk. I've servers with separate backup drives and there complete mess of fragmenetation but I don't really care... my data files are OK
3rd party products will rearrange data on the drive to put most recently used files first and optimise layout; I'm sure the inbuilt just makes files contiguous.
-
I use diskeeper for this. I tested this product a lot on my test SQL servers first and can happily report no harm was caused. I use it on my live boxes now.
The server class offerings for this product allow you set a low cpu priority, which is great for a 24/7 server. You can schedule diskeeper to run by itself and as I prefer I give it a window where it can run. I set up mine to check nightly during non-peak times but before my maintenance jobs run. I believe you can skip files if your really worried about it. Defragging MDFs and LDFs first with diskeeper actually helps improve SQLs internal defragging.
I'd read this http://www.sql-server-performance.com/sql_fragmentation.asp however bear in mind that when I emailed diskeeper to make sure they had no problems running on a machine with SQL, they pointed out that article, which I had already read, and they suggested that they wrote it. Bias or not I'm quite happy with diskeeper.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply