Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
Hi Aaron, when you say recreate the tables, are you talking about dropping and recreating the primary keys in TSQL ? what would I do for any heaps ? – DamagedGoods Aug 23 '12 at 16:26
CREATE INDEX WITH DROP_EXISTING. Why do you have heaps? How big are they? – Aaron Bertrand♦ Aug 23 '12 at 16:28
1  
Taking the database offline and running a file system defrag against the file is not going to treat the fragmentation inside the file. I guarantee you that if you are having performance problems the fragmentation in the database is what you want to focus on, not the file-level fragmentation. – Aaron Bertrand♦ Aug 23 '12 at 16:29
ok thanks, looks like this is going to take a bigger window than I had originally planned !!! – DamagedGoods Aug 23 '12 at 16:46
1  
Apart from raw resource usage, shouldn't, no. If you do it as an online operation they keep using the "old" version while the new one is created in the background. When the new one is ready it's a simple and transparent metadata switch, then the next query that uses it will use the new copy. You can read this BOL topic for more details. – Aaron Bertrand♦ Aug 23 '12 at 17:10
show 3 more comments
up vote 2 down vote

Yes, your approach is a good way to do it.

Assuming you have enough contiguous free space on your volume so that the MDF can be created as one file.

Also, ensure that you rebuild all indexes first so the data is organised well logically too.

share|improve this answer
Restore with move will remove fragmentation? I don't know that I've ever seen that work. – Aaron Bertrand♦ Aug 23 '12 at 16:21
Physical, on disk, NTFS level fragmentation. Restore will remove this if the MDF +LDF are recreated. Why do you think it won't? – gbn Aug 23 '12 at 16:22
1  
Perhaps, but to what end? If the data is fragmented inside the database, how much do you expect to gain from removing only the physical fragmentation? Moving the objects onto a new filegroup that is properly sized from the start will fix both. – Aaron Bertrand♦ Aug 23 '12 at 16:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.