April 25, 2006 at 10:01 am
Is it safe to defragment drives that hold SQL server data and log files and will it help server performance?
April 25, 2006 at 10:08 am
ntfs fragmentation is not good, especially for data files, log files by their nature may tend to fragment.
W2003 will allow an online defrag but you wouldn't want your users connected. It's best to stop the sql service prior to a defrag. For very large files copying them to another drive and back may be quicker then a defrag.
Technically you will improve performance - try setting growth on files and logs to a fixed size rather than a %age , the fewer growths ( and shrinks ) the less the fragmentation.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 25, 2006 at 10:13 am
Thanks for you prompt reply Colin. Does anyone else perform defrags of there disc drives?
April 26, 2006 at 7:58 am
I prefer to take SQL Server down and run the defrag utility. This will make the database files contiguous in the NTFS file system then proceed with DBCC REINDEX. We only do this when the fragmentation monitor reports heavy fragmentation (usually works out to once a year). It will help performance.
April 26, 2006 at 9:05 am
I typically take SQL server offline - in case this is not possible - you can detach certain DBs at a time in case they are located on separate drives. Defrag the according volume – then reattach the DBs.
For very large volumes I tend to pre-allocate dummy files at the time of starting to use a certain data volume - making sure they are nicely lined up and not fragmented - and when need arrives I switch them for real data files - which at that point can only be allocated in that space and will end up as continuous file
I would perform re indexing and possible file size adjustments before performing a physical file defrag.
May 4, 2006 at 9:20 am
If the DB is going to be just one (only working with enormous data warehouses), I usually set the file size to the available disc space at once. That way I avoid possible fragmentation (except the one the stupid OS sometimes adds upon file creation)
May 4, 2006 at 9:31 am
I have defragmented both ways, on and offline and they both work. The load on SQL Server might be an issue if you try to defragment when it is heavily used, but the utilties should work.
Course, might never finish if you're fragmented enough.
If you can, best to preallocate, or periodically reallocate to be sure you get contiguous space on disk.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply