Very slow db detach

  • I was recently trying to detach a database using:

    ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    EXEC sp_detach_db 'MyDatabase', 'true';

    After letting it run without completing for over an hour, I stopped the execution. I have not had it take anything close to this long with other databases before, and it does not seem to be a common issue. What could be slowing it down? Potentially relevant details:

    -Simple recovery mode

    -mdf file size: ~30GB

    -ldf file size: ~3GB

    -Some clustered indexes are currently very highly fragmented

    -sqlservr.exe was keeping one CPU core 90-100% used throughout the process

    Anyone have any ideas on why it would take so long, or perhaps what I could do to figure out what is taking so long?

    Thanks!

  • Long running transaction that it needs to roll back?

    With Rollback Immediate it will start the roll back immediately. Has to finish it, if there's a long-running, large transaction that could take quite a while.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (5/5/2011)


    Long running transaction that it needs to roll back?

    With Rollback Immediate it will start the roll back immediately. Has to finish it, if there's a long-running, large transaction that could take quite a while.

    I really don't think so. I had checked the Activity Monitor and didn't see anything, no Jobs were running, and nothing that runs on that server takes anywhere near that long, anyway.

    Any other ideas?

  • It doesn't necessarily have to show up in activity monitor. If someone ran a large transaction, the query finished, but the commit never happened, that would require a rollback but would not show up in activity monitor because it's not currently running.

    Could also be lots of dirty data pages necessitating a long checkpoint

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have had similar issues to this when there have been problems on the O/S. For example no available memory left.

  • @chris4600

    I had same issues , the detach query executed for 4 hours but didn't completed.

    The solution worked for me is very simple. I dont know the reason behind it but what I suspect is there may be too many uncommitted transactions in the DB.

    Please restart the server , refresh the DB and try the query again.

    And this time it took less than a minute 🙂

  • Manisha93 wrote:

    @chris4600

    I had same issues , the detach query executed for 4 hours but didn't completed.

    The solution worked for me is very simple. I dont know the reason behind it but what I suspect is there may be too many uncommitted transactions in the DB.

    Please restart the server , refresh the DB and try the query again.

    And this time it took less than a minute 🙂

    It's amazing to me how quickly people jump to that option.  Did you even check with anyone else that might be doing something on the server before taking such rash, if not rude, action?  And why would you wait for 4 hours for it to complete to begin with?

    If you're the DBA, this is why people sometimes hate DBAs.  If you're not the DBA, this is why DBAs sometimes hate people. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Show mercy on newbie 🙂

  • Manisha93 wrote:

    Show mercy on newbie 🙂

    Heh... I did!

    While it may come to having to do a reboot, recommending a reboot without qualification is a bad idea.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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