November 4, 2013 at 11:46 am
I had an event where my database backups had grown considerably. After some searching I found that the culprit was new job that had inserted a number of false records into a table. The table in question had a data size of 3,322,112 KB and a index size 1,262,624 KB according to sp_spaceused. After cleaning up the bad records and rebuilding a fragmented index these numbers shrunk to 334,104 KB and 157,232 KB respectively.
Now the difference does seem to coincide with the dramatic growth. However, I was really expecting for the database backups to drop in size but they have not. I ran a copy backup to test this and it actually grew a GB. I tried running a transaction log back up to make sure that that didn't have anything to do with it, but it seems that there is nothing for me to do to get the backup sizes back to what they once were.
The only thing I can think of is to try and shrink the database, but I would think that the backups wouldn't store the empty space. Also from what I can gather from a query I found here This table is still the largest in the database albeit much smaller than it once was. What am I missing?
November 4, 2013 at 12:22 pm
Backups don't include unallocated pages. Shrink will just re-fragment all your indexes.
Did you rebuild the clustered index for that table? Does it have a clustered index?
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
November 4, 2013 at 12:30 pm
No, not yet. This sounds very very plausible. I just rebuilt the clustered index. Trying another copy-only backup.
Thanks,
November 4, 2013 at 12:58 pm
No luck. The table does have two non-clustered indexes and on clustered. Rebuilding the clustered index seems to have no effect. The odd part is that when I start the back up the file appears to be about the original size before this whole mess, but once it finishes it bounces to it's bloated size.
On a whim I just rebuilt the two non-clustered indexes. Also I tried just "overwriting" the database back up I had from before. I am trying again with a fresh new file. Not sure where this will land me. I am expecting square one.
edit : original to bloated.
November 4, 2013 at 1:45 pm
Are you appending multiple backups to the same file? If you back up repeatedly to the same file that's the default, append.
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
November 4, 2013 at 2:08 pm
I think I see the problem. This is a mirrored database. The mirror is suspended and I can't restart it. I assume the databases have fallen too far out of sync to be salvaged. I am going to attempt to kill the mirroring and then re establish mirroring.
I wish I saw that earlier.
Edit : Missed a conjugation.
November 4, 2013 at 2:15 pm
Possibly large amount of log being included in the backup. Guessing though.
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
November 4, 2013 at 2:36 pm
Yes I think you are right. Once I killed the mirroring the backup was < 4GB which is about right. Now I just need to reestablish mirroring and I am golden...fun times.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply