June 8, 2006 at 2:48 pm
Some of my SQL Server 2000 MDF/LDF files are fairly fragmented (analyzed using built-in Defragmenting tool on Windows Server 2003 box).
I asked my DBA and Server Admin to look into defragmenting the files.
My DBAs said that defragmenting could hurt performance since restructering the layout of the MDF files could mix up the way the clustered indexes are stored and re-order the data.
I am 99.999% sure that they are completely wrong, otherwise, any file that gets defragmented could get mangled. I have used DiskKeeper often at other jobs without incident. I was searching for some information to dispute this absurd claim, but can't find anything specific. I'm not surprised that I can't find anything since this is not something anybody else in the world has probably thought of.
Question 1 - Can somebody point me to a source or provide me details so I can prove they are 100% incorrect.
Question 2 - All our database files are on a SAN. I have read some articles saying that SAN drives don't have a problem with fragmentation and the disk head performance isn't affected. My gut tells me that this is not completely true and was looking for specific performance metrics anybody has compiled that shows defragmentation on a SAN drive does help performance.
June 9, 2006 at 7:38 am
fragmented .mdf & .ldf files? I would suggest backing the databases up, then drop them and delete the physical files, create some new database objects in their place with the correct sizing to effect a one large contiguous file and then restore from the backup. That is, assuming the databases are not too big and some downtime is allowed. I used this method on several occassions and it works. I wouldn't attempt to defrag the drives.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply