Extreme wait-time when taking a SQL Server database offline

Erik_16942 0
Viewed 339107

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?

sql-server-2005 database performance
17 Answers

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.

Erik_169420

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
user_275350

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.

user_652230

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.

user_913350

I tried all the suggestions below and nothing worked.

  1. EXEC sp_who

  2. Kill < SPID >

  3. ALTER DATABASE SET SINGLE_USER WITH Rollback Immediate

    ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

    Result: Both the above commands were also stuck.

4 . Right-click the database -> Properties -> Options Set Database Read-Only to True Click 'Yes' at the dialog warning SQL Server will close all connections to the database.

Result: The window was stuck on executing.

As a last resort, I restarted the SQL server service from configuration manager and then ran ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE. It worked like a charm

user_66380680

Next time, from the Take Offline dialog, remember to check the 'Drop All Active Connections' checkbox. I was also on SQL_EXPRESS on local machine with no connections, but this slowdown happened for me unless I checked that checkbox.

user_50039100
Related