• Stack Overflow Public questions & answers
  • Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
  • Talent Build your employer brand
  • Advertising Reach developers & technologists worldwide
  • About the company

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Extreme wait-time when taking a SQL Server database offline

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?

Erik Forbes's user avatar

18 Answers 18

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:

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:

And use whatever SPID you find in the following command:

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

Abel's user avatar

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

To find connections, use sys.sysprocesses

To force disconnections, use ROLLBACK IMMEDIATE

gbn's user avatar

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.

Ajay's user avatar

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.

Rudy's user avatar

execute the stored procedure sp_who2

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

woodwa's user avatar

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

nzeemin's user avatar

In my case I had looked at some tables in the DB prior to executing this action. My user account was holding an active connection to this DB in SSMS. Once I disconnected from the server in SSMS (leaving the 'Take database offline' dialog box open) the operation succeeded.

RamenNoodle's user avatar

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.

yetanotherdave's user avatar

Closing the instance of SSMS (SQL Service Manager) from which the request was made solved the problem for me.....

Armand G.'s user avatar

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.

Dan's user avatar

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

Steve Woods's user avatar

I tried all the suggestions below and nothing worked.

Kill < SPID >



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

Viraj A's user avatar

In SSMS, set the database to read-only then back. The connections will be closed, which frees up the locks.

In my case there was a website that had open connections to the database. This method was easy enough:

zacharydl's user avatar

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.

craig's user avatar

In my case, the database was related to an old Sharepoint install. Stopping and disabling related services in the server manager "unhung" the take offline action, which had been running for 40 minutes, and it completed immediately.

You may wish to check if any services are currently utilizing the database.

Jonathan's user avatar

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.

Brett Drake's user avatar

SSMS, especially if running it from your own desktop remotely and not directly within the database server, can be a reason for the long delays in detaching a database. For some reason SSMS may not be able to disconnect any existing "connections" to the database.

We found the process was almost instant when we did it directly from the database server itself. And in fact it killed the attempt from my own desktop SSMS session, and it "took over" and detached the database.

Nothing else suggested here worked.

Fandango68's user avatar

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

Java Main's user avatar

Your Answer

Sign up or log in, post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service , privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged database sql-server-2005 performance or ask your own question .

Hot Network Questions

sql tasks take offline

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

How to Take SQL Server Database Offline

By: Joe Gavin   |   Updated: 2020-05-15   |   Comments (2)   |   Related: More > Database Administration

You need to take a SQL Server user database offline.

These are a few reasons you may need to do this and here are just a couple of them:

As with most problems there is more than one way to solve it. Three methods are listed here to help you decide which one to use:

We'll step through each of the three methods, so you'll be able to decide which one is best for your purposes.

The following versions were used in this tip:

CAUTION: Backup any database you're taking offline and test restoring it somewhere else before continuing. You just never know.

Take SQL Server Database Offline with SQL Server Management Studio (SSMS)

To start SQL Server Management Studio

Start SQL Server Management Studio (SSMS) from Shortcut

Or alternatively, as the SQL Server Tools path will be appended to your users %PATH% variable:


Start SQL Server Management Studio (SSMS) from executable

Object Explorer will likely open automatically, but if it doesn't do the following:

Show Object Explorer

Now, we'll connect to the database engine.


Connect  to Database Engine

The name of the database we're going to take offline is called MyDatabase.

Start Database Offline

If the Status is 'Ready', there are no connections in the database.

Database Offline screen

But, if the status is 'Not Ready' as shown below.

Shows if there are active connections in database

As we can see in our example, it's telling us there is one connection in the database we want to take offline. The message box tells us to close the connections or select the 'Drop All Active Connections' box. We can take care of this in one of two ways.

New Query

Look for connections in database

Next, run kill with the spid on any that are in the database. Here we only have one with a spid = 57.

Kill connection(s)

Verify there are no more connections

Go back to the Object Explorer.

In the Take Database Offline window, do the following:

Take database offline

One thing to note here. We could have just checked 'Drop All Active Connections' to force connections out. But the SQL Server is keeping us from taking the database offline for a reason, which is to protect us from ourselves. It's just safer to see what connection(s) are in the database first. If you were accidentally attempting to take an active production database offline you would probably be able to catch the mistake before you made it.

Look in the Object Explorer to be sure the database shows (Offline).

Verify database offline

If not, do the following:

Refresh Databases list

Take SQL Server Database Offline with T-SQL

We'll still use SSMS, but just the Query window.

New Query

Look for connections in database

Next, run the kill command with the spid on any that are in the database. Here we only have one with a spid = 55.

Kill connection(s)

Take the database offline with the following T-SQL:

Take database offline

We didn't get any errors, but here's one way to verify the database is offline:

Verify database offline

Take SQL Server Database Offline with dbatools

We've seen how to take a database offline with SQL Server Management Studio and T-SQL, and now we'll see how to do it with dbatools that you download here .

To get started:


We'll run Get-DbaProcess, tell it the SQL Server name –SqlInstance (note:  .\InstanceName won't work here if you're using a named instance like I am) and pass the output to the Select command adding a pipe (|) and the three fields we want.

Look for connections in database

As we can see, there is one connection in the database.

If we're sure we can kill these connection(s), we'll use the same switches for Get-DbaProcess, but will redirect the output to Stop-DbaProcess.

Kill connections

The Status field shows we've killed the process(es).

We're now ready to take the database offline.

Use Set-DbaState with the same -SqlInstance and -Database switches. We won't pipe the output anywhere, but add the -Offline at the end.

Take database offline

You'll see the Status field shows it offline.

We've seen the safer way to check for what connections are in the database and how to kill them. And finally, here is how to force the connections out automatically by adding the -Force to the end of the previous command line.

Force database offline

We've seen how to take a database offline using SSMS, T-SQL and the dbatools PowerShell scripts. All three of these methods do the same thing. You can pick whichever one you want based on if you just want to do it the quickest way, or how comfortable you are with T-SQL or PowerShell.

Here are some links to other MSSQL Tips with further info:

Related Articles

Popular articles.

get scripts

About the author

MSSQLTips author Joe Gavin

Comments For This Article

get free sql tips

My Tec Bits

Taking Database Offline In SQL Server

You may come across situations to take one of the SQL Server databases offline. For me, I took older development and test versions of databases offline to avoid developers and testers using the wrong database. A database can be taken offline either using transact-SQL or by using SQL Server management Studio ( SSMS ). I prefer taking database offline before dropping or deleting them. This is to avoid any mistakes or loosing data prematurely. Couple of times I’ve to take back the offline database online to refer some testing data. After keeping the database offline for a month or so, I usually drop them from the server. Below are couple of simple methods to take the database offline. The next article will be about bringing back the database online and will write another article on attaching and detaching databases and database on emergency mode .

Taking Database Offline Using T-SQL

In management studio (SSMS), open a query window and execute the below T-SQL statement.

I haven’t specified any rollback transactions in this ALTER DATABASE T-SQL statement. If the rollback termination option is not specified, this statement wait for all the transactions to complete and then takes the database offline. In case if there is a transaction which is neither committed or roll-backed, then the statement wat for it indefinitely. So, if you want to rollback all the transactions immediately, then use the termination statement WITH ROLLBACK IMMEDIATE in the alter statement as below.

Or, if you want to allow the alter statement to wait for some time (say 15 seconds…) for the transactions to complete before taking the database offline, then use the termination statement WITH ROLLBACK AFTER 15 SECONDS along with the alter statement as below. In this case, if there any active transactions against the database, the alter statement will wait for 15 seconds for the transaction to commit or rollback. After 15 seconds, the active transactions are roll backed and the database is taken offline.

Taking Database Offline using SSMS

Taking Database Offline in SQL Server

Taking Database Offline in SQL Server 03

NOTE: Follow the steps in this article to bring back the database online.

You may also like...

Leave your thoughts... cancel reply.

This site uses Akismet to reduce spam. Learn how your comment data is processed .


  1. Liste der SQL Tasks

    sql tasks take offline

  2. SQL Server Health Check Checklist

    sql tasks take offline

  3. SQL Tutorial Offline

    sql tasks take offline

  4. How to Take SQL Server Database Offline

    sql tasks take offline

  5. How to take MSSQL database offline or bring it online?

    sql tasks take offline

  6. SQL SERVER DBA: Change the Virtual IP Address for a SQL Server Failover Cluster

    sql tasks take offline


  1. Next

  2. SWFL Bald Eagles 02/14/2023 23:01

  3. Chhalka Chhalka Re

  4. Execute Package Task

  5. Kesariya Dance Mix

  6. Multiple Ways To Start & Stop the Sql Services


  1. how to stop the process take table offline in sql server

    1 If you did this via SSMS, just shut down SSMS - that'll kill the process you started by the "take offline". Then, next time, check this: select db_name (dbid) , * from sys.sysprocesses where db_name (dbid) like 'theNameOfTheDbYouWantToTakeOffline' ....and verify these processes can be safely stopped....stop them, and go for it.

  2. Extreme wait-time when taking a SQL Server database offline

    If Take DB Offline process is still running, for dev machines you can kill it from Task Manager and run above command. – Null Head Jun 21, 2015 at 23:43 1 If you run the KILL command and get the message "Cannot use KILL to kill your own process.", make sure you are using master database to run the command – Jarrod Apr 13, 2018 at 12:26

  3. How to Take SQL Server Database Offline

    Take SQL Server Database Offline with T-SQL We'll still use SSMS, but just the Query window. Right click on the server New Query EXEC sp_who2 in the query window F5 (or click Execute) Look under the DBName column for any referenced to the database we're taking offline and note the corresponding number under the SPID column

  4. Taking Database Offline In SQL Server

    Login to SQL Server Management Studio. In the Object Explorer, select the database you want to take offline and right-click. In the right-click menu go to Tasks >> Take Offline. In the pop-up window, choose the check box under the Drop All Active Connections and click OK. The database will now taken offline.