Sql-server – Removing physical fragmentation

fragmentationsql serverstorage

If I have inherited db that has grown to 400GB 1 MB chunk at at time. Is an offline defrag the only way to remove all the physical fragmentation. Another issue I have is that the data and log files are mixed and on the wrong RAID drives.

Can I kill 2 birds with 1 stone by backing up the db, delete it and restore it using WITH MOVE to put the files on the correct RAID drives. Will this eliminate the physical fragmentation?

Best Answer

No, backup / restore will keep all the fragmentation. Probably better to add a filegroup with files in the new location, and recreate all of your user tables on the new filegroup (by recreating the indexes with DROP_EXISTING, and as an online operation if possible). You won't be able to eliminate the original files entirely, but if you've moved all the user objects, you should be able to shrink the primary data file down to system objects only.

If you then want to move the primary MDF file and log file to the new RAID drives, you can do this with ALTER DATABASE - take the database offline, alter the individual file locations, physically move the files, then bring the database back online again.

Related Solutions

SQL Server 2005 – Defrag vs Backup and Restore

There is a great discussion of this topic here at ServerFault in which the 3 forms of fragmentation are discussed. I always have to remind myself to search for the answer before I post a question as it saves me so much time.

Short answer (tl/dr):

It will remove physical fragmentation but not logical or VLF fragmentation within the log file.

Physical Fragmentation:

Backing up and restoring will remove any physical fragmentation. That is, fragmentation that occurred on the NTFS partition as a result of your database growing incrementally over time and contiguous space not being available as a result.

Other Fragmentation:

You will still have index fragmentation and VLF Fragmentation within the database. Paul Randall stressed not using Windows Defrag at his SQLSkills Immersion Events, and how it could lead to database corruption so I think you're on the right track.

Background:

Physical fragmentation will operate on the disk where the MDF/NDF/LDF files occupy. Since SQL Server does not track this information as it would be useless. We want to be able to restore databases to any hard drive as long as it has space, we're not concerned with where it gets located on the disk so much unless you're short writing...but that's a exception to the rule.

Sql-server – SQL services wont start due to incorrect drive letter after windows restore

I assume you've restored entire partitions, but the partition drive letters are now wrong.

If SQL is looking for I: then you have three choices

a) Fix the drive letter of the partition that's now got the data. You said this is not an option. Is that because I: is already taken, or because something else is now looking for this data based on its new drive letter (E:)

b) Add an additional drive letter for the partition - nothing stops you referring to the same partition by more than one drive letter. This isn't an option if I: is already taken and you can't move that data from I:

c) Create a symbolic link on your new I: drive to point things back to what's now on E:. Start an elevated command prompt. Then type

I:
cd "\Program Files\"
mklink /d "Microsoft SQL Server" "E:\Program Files\Microsoft SQL Server"

If you already have a Microsoft SQL Server folder on your new I: then change the mklink command to make the MSSQL.1 folder as the symlink rather than the Microsoft SQL Server folder.

Best Answer

Related Solutions

SQL Server 2005 – Defrag vs Backup and Restore

Sql-server – SQL services wont start due to incorrect drive letter after windows restore

Related Question