DBCC SHRINKFILE fun

  • SQL 2008R2 Enterprise. Production hardware. Datafile on a SAN. Database mirrored high safety. Nothing else running.

    Purged massive amounts of application logging from inside the database. DB never gonna be this big again.

    DB at 500GB, actual data in the 50GB range. Targeting 100GB final size.

    Going to shrink it in small slices.

    This will sound crazy but I'm honest here:

    Trying to just shrink it by (1) MB (MEGAbyte) at a time takes 6 hours. :w00t:

    No load on the system.

    No blocking registered.

    And it reports a 99.993% complete status within 10 seconds.

    It takes just 6 more hours to finish that last .007%! :hehe:

    Anyone know of a way to "look in" to what the DBCC is chewing on for that long? It is churning the CPU and disks the whole time - not crazy high - but over 6 hours it accumulates. CPU count at 500k, Reads are at 60million...

    My ideas:

    USE DBCC PAGE / DBCC IND to try and look at the last 1MB worth of pages in the data file to see what kind of data lives there. Now, how in the world would I figure out the page number of the "last" pages of the file so I could look?

    6+hrs is extreme for 1MB of movement even if it's LOB data.

    If it was only happening 1 time it would be ridiculous, but I could move on

    The problem is: it seems to be happening again. No errors. Just crazy slow.

    6hrs for the first shrink of 1MB.

    We're at 4+ hours now for the second shrink of 1MB.

    Preceding this I was able to shrink it several times by 100MB inside of 10 seconds. So I know it can be done. One of my attempts at shrinking by 100MB eventually just spun and spun... so I stopped it and reduced it to 50.. then 20.. then 10.. then 1. And here we are.

    Any advice worth passing to me or am I in uncharted waters?

    Thanks in advance,

    Jim

Viewing 0 posts

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