June 22, 2009 at 10:11 am
Our database is already 90% fragmented. Does SQL Server backup removes the fragmentation ? If it does, then all I need to do is just backup and restore. Any comments ?
June 22, 2009 at 10:19 am
jimmycjen (6/22/2009)
Our database is already 90% fragmented.
Index fragmentation or file-system fragmentation?
Does SQL Server backup removes the fragmentation ?
All SQL backup does is read through the database file and write out a backup file. It makes no significant changes to the database (just sets some last backup info in the header)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2009 at 11:42 am
I mean file fragmentation.
I noticed the backup file size is always smaller than the actual database file. That's why I thought if SQL Backup will defrag its files.
What if I backup the database, then drop it and restore it, will database file remain fragmented ?
Thanks for replying.
June 22, 2009 at 2:05 pm
jimmycjen (6/22/2009)
I noticed the backup file size is always smaller than the actual database file.
Because the database file may have some empty space and the backups just contain the data, not the empty space
What if I backup the database, then drop it and restore it, will database file remain fragmented ?
Maybe. It depends what the file system looks like. If, on disk, there's no contiguous free space for the file to go, then it will be fragmented because there's no way it can be put on disk in a single chunk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply