Post reply

Question regarding DBCC ShrinkFile... db grew ?

  • November 23, 2009 at 10:15 am

    #137380

    Hi;

    I had a question regarding dbcc shrinkfile and I'm wondering if anyone else has experienced what I have recently and what they did.

    I never have had to shrink a database at my current job (had to in the past due to various reasons, like upgrades, dev changing indexes, and tables, etc) so its been a while since I've done it.

    So recently I needed to shrink a database that was 100 GB in size, as we were clearing a temp table that filled up for a specific case, and we needed to reclaim the space.

    I truncated the table and ran a few rebuilds on some large indexes. The shrinkfile said I could reclaim about 7 GB. I started it around 11:30 pm, and it ran until 6:30 am, when it crapped out because something changed.

    The weird thing, which I haven't seen before is the database grew, to about 120 GB and the tempdb grew to about 60 GB. Now the database is showing there is no free space (about 200 MB).

    I know I can shrink the tempdb with single user mode, so I'm not worried about that (on average the temp db is around 9 GB for this server).

    But I'm at a loss as to understanding what happened to my database, and where to go from here. When it was growing I assumed it was 'shuffling things around' and it would clear itself up when it was done.. which didn't happen of course.

    7 GB doesn't seem like much but due to circumstances we still need to try and reclaim this, and at this point there is another 20 GB that need to be reclaimed now too. I was thinking re-creating some indexes might help, is this a good idea?

    This is a database that is on SQL Server 2005, but it is in compatibility mode 80 (db was migrated from 2000). I can't change this for application reasons.

  • November 23, 2009 at 11:03 am

    #1083066

    Just to reply to my own topic, I'm a bit of a dumb*ss, reading over the article on "dm_db_index_physical_stats"

    It states.. "Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is moved partly or completely during the shrink operation. Therefore, if a shrink operation must be performed, you should not to do it after fragmentation is removed.".

    D'oh.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply