DBCC shrinkfile - rollback just hanging

  • Hello,

    I canceled a shrinkfile (emptyfile) some 2 days ago, but it is still showing as klled/rollback. Would it be OK to restart the instance to get rid of it? I have a backup which I can restore, but will the db come back up, or will it be marked as suspect?

    I need to get this DB back before Monday morning. Many thanks

  • is the database huge. if so then it will take time to recover. even if you are ging to restart your sql services they wil be in recovery mode and hence yu not be able to access the database until they are completely recovered. the best option if you want to have the database ready is to restore from the latest backup that you have.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Given that it's been a couple of days since you posted this issue it's likely that you're going to need to restart SQL Server if you're still in rollback - every so often (rarely) I've seen SQL get into this state, usually you're OK to restart SQL server to no ill affect as the process you're seeing is just hung/not real. Don't know why these happen, but they do. Shrink file seems to be somewhat prone to this particular behavior when it fails, don't know why, but experience shows that a restart of SQL will usually clear it.

  • Make sure to notify your end users about the restart of services BEFORE you do it. They may lose data if you don't. (Data not commited to the TransLog, that is).

    Or, better yet, do the restart during maintenance hours when no one should be on the system anyway.

    Regardless, do a check of the DB after you've restarted to verify that the database looks good (not suspect) and that everything you think should be there is actually there. And if this is a production DB and it's spent two days in rollback mode, find someone who might have entered data during those 2 days and have them check that their data is actually there like it should be.

    A little CYA never hurts. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 4 posts - 1 through 3 (of 3 total)

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