July 10, 2008 at 11:31 am
I have a database that was 2.5 gig running on SS2000 sp4. I restored it from a backup onto a new instance on a new server running SS2005 standard edition. I rebuild the indexes and updated statistics. After this, the database has been running for two days and the file size has grown to 6x its original size. The number of rows added to the database are consistent with what we would expect. The results from checking space used shows rows, reserved, data and unused with the rows consistent with what we expect to see based on its pre-migration row count per table but the reserved, data and index sizes are all extremely high. The unused space is very low so its not something that can be corrected by shrinking the file size.
Has anyone encountered this problem before? Your help is appreciated.
'nix
July 10, 2008 at 11:38 am
Haven't seen that. Did you create the database before restoring? could the sizing have been wrong?
July 10, 2008 at 11:56 am
nope, it was a straight up restore from a running sql 2000 machine. The db is actually a template that is used for different companies and has been in production for some time.
Could it be something to do with the DISK IO causing the file size to explode like this? Were looking at how to get the data back off SS2005 until we can figure out what happened but if it is actually stored at this size, even DTS the data back to a SS2000 database will just transfer the same size data as what I have now.
The size was correct after the restore, only yesterday after rebuilding indexes did I see a sudden surge in file size that has not stopped. Good thing we have extra space on our drives...margin of error is a good thing.
'nix
July 10, 2008 at 12:08 pm
The indexing is a logged operation, so you end up with 2x index size (+ pad) when it's working, so I can see if doubling in size if you have a large table or two, but not 6x.
that's strange.
I'd be tempted, if you can, to shrink it down to data size + largest index size, and then reindex to see if it happens again.
July 10, 2008 at 12:14 pm
I dont think I can since the unused space reserved = Data + Index size with very little unused space. Will shrinking even work? Plus the data size is much higher than it should be even without the indexes. I thought similarly that it could be something screwy happened when I reindexed so I reindexed again and its the same size, it looks like real data stored in the .mdf but I have no idea where it could have come from. Our largest table on this db is 144k. In the last two days its grown only 4700 records but the size of the data in that table has grown 6x.
Makes no sense.
'nix
July 10, 2008 at 2:55 pm
Ok, got it. The default fill factor written into the clustered indexes when originally created was 10% on a PK identity field. Never going to do a split and exploded the size of the database when reindexed. Didnt show up on my script to show empty space as that only shows empty pages and each page has 10 filled.
Rebuilt them all explicitly changing to 90% and the database size is back down to where its supposed to be (after shrinking of course)
You learn something new every day. You really have to know this stuff at a granular level. I didnt know that I was looking at only empty pages, I thought it was empty extents.
Thanks Steve and to our consultant Bill who figured it out....Still not 100% sure why it blew up now and not when I rebuilt it (weekly) on the 2000 instance though but making that change brought the size down and everyone is happy (today)
🙂
'nix
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply