Extreme wait-time when taking a SQL Server database offline

0 votes
asked Apr 30, 2009 by erik-forbes

I'm trying to perform some offline maintenance (dev database restore from live backup) on my dev database, but the 'Take Offline' command via SQL Server Management Studio is performing extremely slowly - on the order of 30 minutes plus now. I am just about at my wits end and I can't seem to find any references online as to what might be causing the speed problem, or how to fix it.

Some sites have suggested that open connections to the database cause this slowdown, but the only application that uses this database is my dev machine's IIS instance, and the service is stopped - there are no more open connections.

What could be causing this slowdown, and what can I do to speed it up?

15 Answers

0 votes
answered Apr 30, 2009 by km

Do you have any open SQL Server Management Studio windows that are connected to this DB?

Put it in single user mode, and then try again.

0 votes
answered Apr 30, 2009 by gbn

There is most likely a connection to the DB from somewhere (a rare example: asynchronous statistic update)

To find connections, use sys.sysprocesses

USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')

To force disconnections, use ROLLBACK IMMEDIATE

USE master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
0 votes
answered Apr 30, 2009 by erik-forbes

After some additional searching (new search terms inspired by gbn's answer and u07ch's comment on KMike's answer) I found this, which completed successfully in 2 seconds:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

(Update)

When this still fails with the following error, you can fix it as inspired by this blog post:

ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.

you can run the following command to find out who is keeping a lock on your database:

EXEC sp_who2

And use whatever SPID you find in the following command:

KILL <SPID>

Then run the ALTER DATABASE command again. It should now work.

0 votes
answered Apr 30, 2009 by yetanotherdave

anytime you run into this type of thing you should always think of your transaction log. The alter db statment with rollback immediate indicates this to be the case. Check this out: http://msdn.microsoft.com/en-us/library/ms189085.aspx

Bone up on checkpoints, etc. You need to decide if the transactions in your log are worth saving or not and then pick the mode to run your db in accordingly. There's really no reason for you to have to wait but also no reason for you to lose data either - you can have both.

0 votes
answered Apr 20, 2013 by rudy

In my case, after waiting so much for it to finish I had no patience and simply closed management studio. Before exiting, it showed the success message, db is offline. The files were available to rename.

0 votes
answered Apr 10, 2014 by nzeemin

In SSMS: right-click on SQL server icon, Activity Monitor. Open Processes. Find the processed connected. Right-click on the process, Kill.

0 votes
answered Apr 23, 2014 by dan

To get around this I stopped the website that was connected to the db in IIS and immediately the 'frozen' 'take db offline' panel became unfrozen.

0 votes
answered Apr 2, 2015 by craig

For me, I just had to go into the Job Activity Monitor and stop two things that were processing. Then it went offline immediately. In my case though I knew what those 2 processes were and that it was ok to stop them.

0 votes
answered Apr 2, 2015 by java-main

In my case i stopped Tomcat server . then immediately the DB went offline .

0 votes
answered Apr 17, 2015 by steve-woods

Also, close any query windows you may have open that are connected to the database in question ;)

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...