Why does copy of SQL database have fragmented indexes?
We have a large SQL database, and every night a copy is made. We actually work with the copy.
My question is: Why is our copy of the database fragmented? I did a brief look and discovered heavy index fragmentation in some of the indexes.
Now I know file fragmentation is a different thing, but for file fragmentation back in the old days we would sometimes just write the whole disk to tape (that's how old this method is) and then write the whole thing from tape back to disk, and that would take care of our file fragmentation (well for a week at least. Then they'd have me stay up all night Sunday night and do it again...)
So my question is, when a SQL database is copied, why aren't the fragmented indexes magically unfragmented for us?
(I don't know how the IT guys make the copy, but the database is about 73GB. We have Microsoft SQL Server, and I think it's the 2008 version.)
(Perhaps I should also ask, is there a way they could copy the database and have the indexes magically defragmented for us during the copy?)
My question is: Why is our copy of the database fragmented? I did a brief look and discovered heavy index fragmentation in some of the indexes.
Now I know file fragmentation is a different thing, but for file fragmentation back in the old days we would sometimes just write the whole disk to tape (that's how old this method is) and then write the whole thing from tape back to disk, and that would take care of our file fragmentation (well for a week at least. Then they'd have me stay up all night Sunday night and do it again...)
So my question is, when a SQL database is copied, why aren't the fragmented indexes magically unfragmented for us?
(I don't know how the IT guys make the copy, but the database is about 73GB. We have Microsoft SQL Server, and I think it's the 2008 version.)
(Perhaps I should also ask, is there a way they could copy the database and have the indexes magically defragmented for us during the copy?)
ASKER
That's probably it. They probably use BACKUP and RESTORE. I guess BACKUP and RESTORE does too good a job of duplicating exactly the database, including all the fragmentation.
Our IT department is too afraid to defragment the indexes. They fear data loss.
I have a 140MB table with 98% fragmentation. [I didn't even know it was possible to be that fragmented!]
I'm wondering if there's a way I can BACKUP and RESTORE a copy of the database on my local computer so I can defragment it and see if there's a performance improvement.
Our IT department is too afraid to defragment the indexes. They fear data loss.
I have a 140MB table with 98% fragmentation. [I didn't even know it was possible to be that fragmented!]
I'm wondering if there's a way I can BACKUP and RESTORE a copy of the database on my local computer so I can defragment it and see if there's a performance improvement.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you everybody. Now I better understand how data is stored in files, and I will look into the shrinking thing which sounds like something they would do.
ASKER
OMG thank you so much for mentioning the database shrinking! I looked into it and they have AutoShrink turned on for the databases which are highly fragmented!
Perhaps you re-index your source database on a different schedule than when you copy it off?
Not sure if I'm hitting the mark, but these are just some things to rule out.