Advertisement
Advertisement


Extreme wait-time when taking a SQL Server database offline


Question

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?

2009/04/30
1
278
4/30/2009 5:53:15 PM

Accepted Answer

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.

2016/02/03
409
2/3/2016 5:13:24 PM

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
2012/01/23

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.

2016/05/30

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.

2013/08/20

execute the stored procedure sp_who2

This will allow you to see if there is any blocking locks.. kill their should fix it.

2017/01/24

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

2014/12/10

Source: https://stackoverflow.com/questions/808232
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]