Very slow db detach

Question

Very slow db detach

chris4600

Old Hand

Points: 322
More actions
May 5, 2011 at 10:46 am

#249918

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!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

chris4600 Old Hand Points: 322 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 4

I have had similar issues to this when there have been problems on the O/S. For example no available memory left.

Manisha93 Newbie Points: 3 More actions · Answer 5

@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 🙂

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

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)

Manisha93 Newbie Points: 3 More actions · Answer 7

Manisha93

Newbie

Points: 3

August 2, 2022 at 10:27 am

#4071278

Show mercy on newbie 🙂

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 8