- 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?
- sql-server-2005
- performance
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.
- 35 If this is not working (a lock could not be placed), also try the solution in stackoverflow.com/questions/4673065 . – nalply Jul 12, 2012 at 10:04
- 3 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
- What's really annoying about this is that 99% of the time, allowing a connection to block taking the DB off-line is unwanted behavior. When I want the DB off-line, I want any connections to be cut. – EvilSnack Jun 2, 2021 at 18:20
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
- 8 +1 because the process query lets you know what's connected to this database. in my case it was a rogue employee with SSMS open :) – MikeMurko Nov 29, 2012 at 22:08
- 3 In my case I was the rogue with a query analyzer window open – dellyjm Jun 16, 2015 at 14:03
- 1 In my case the developers had a production main website for a very well known bank pointing at a database lebelled OLD – ZZ9 Apr 5, 2016 at 18:14
- 4 If it says ALTER DATABASE failed because a lock could not be placed on database command KILL <SPID> will help – Muflix Sep 16, 2016 at 8:07
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.
- 3 ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate – u07ch Apr 30, 2009 at 18:02
- KMike - the only connection I have is open to the Master database, not the database I'm trying to take offline. – Erik Forbes Apr 30, 2009 at 18:04
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.
execute the stored procedure sp_who2
This will allow you to see if there is any blocking locks.. kill their should fix it.
In SSMS: right-click on SQL server icon, Activity Monitor. Open Processes. Find the processed connected. Right-click on the process, Kill.
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.
- Same with me. Then I reconnected, changed the active database to master and run the following command: ALTER DATABASE XXX SET OFFLINE WITH ROLLBACK IMMEDIATE – cskwg Mar 4, 2020 at 19:50
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.
- 2 Sage advice - thank you - but in this case the data is expendable as it's a development database that is being restored to. – Erik Forbes Apr 30, 2009 at 18:28
Closing the instance of SSMS (SQL Service Manager) from which the request was made solved the problem for me.....
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.
Also, close any query windows you may have open that are connected to the database in question ;)
I tried all the suggestions below and nothing worked.
- EXEC sp_who
Kill < SPID >
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
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:
- 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.
- Re-open Options and turn read-only back off
- Now try renaming the database or taking it offline.
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.
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.
- 1 Run sp_who2 to see what processes are using the database and use kill <PID> to stop them. – Eric Kigathi Oct 10, 2016 at 23:20
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.
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.
In my case i stopped Tomcat server . then immediately the DB went offline .
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 .
- The Overflow Blog
- How Intuit democratizes AI development across teams through reusability sponsored post
- The nature of simulating nature: A Q&A with IBM Quantum researcher Dr. Jamie...
- Featured on Meta
- We've added a "Necessary cookies only" option to the cookie consent popup
- Launching the CI/CD and R Collectives and community editing features for...
- Staging Ground Beta 1 Recap, and Reviewers needed for Beta 2
- The [amazon] tag is being burninated
- Temporary policy: ChatGPT is banned
Hot Network Questions
- Tips for golfing in SVG
- Replacing broken pins/legs on a DIP IC package
- Do new devs get fired if they can't solve a certain bug?
- How can this new ban on drag possibly be considered constitutional?
- Can I tell police to wait and call a lawyer when served with a search warrant?
- Checking system vs. SEPA and the like
- Can archive.org's Wayback Machine ignore some query terms?
- Knocking Out Zombies
- Is there a solution to add special characters from software and how to do it
- Did this satellite streak past the Hubble Space Telescope so close that it was out of focus? If so, how close was it?
- Follow Up: struct sockaddr storage initialization by network format-string
- Why are physically impossible and logically impossible concepts considered separate in terms of probability?
- Is it suspicious or odd to stand by the gate of a GA airport watching the planes?
- How does fire heat air?
- Butterfly Theorem
- What is a word for the arcane equivalent of a monastery? A place where magic is studied and practiced?
- What sort of strategies would a medieval military use against a fantasy giant?
- Where does this (supposedly) Gibson quote come from?
- Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4?
- Counting Letters in a String
- Acidity of alcohols and basicity of amines
- Does Counterspell prevent from any further spells being cast on a given turn?
- Short story taking place on a toroidal planet or moon involving flying
- Whats the grammar of "For those whose stories they are"?
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:
- You're 99% sure a database is no longer used, but you want to 'scream test' it for a while to be sure before dropping it
- You've moved a database to another server and think you have all the connections to it repointed to the new one, but want to guarantee a user can't access the old one
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:
- Free download
- Probably the easiest and quickest way if you just need to take one database offline
- Doesn't require you to be very comfortable using T-SQL or PowerShell
- Gives you an easy way to see if there are connections in the database before trying to take it offline
- You just need to check off a box to force connections out of a database
- Run in Query window that is part of Management Studio
- Still pretty easy and quick
- Just requires a minimal knowledge of T-SQL
- Use if you're writing a T-SQL script to take one or more databases offline
- Still easy to force connections out of a database with an extra line of T-SQL
- Free PowerShell module
- Still easy and quick
- Just requires a minimal knowledge of PowerShell and dbatools
- Very easy to script out taking one or more databases offline
- Still easy to force connections out of a database with a switch
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:
- SQL Server 2017 CU19 Developer Edition
- SQL Server Management Studio 18.4
- PowerShell 5.1.17763.1007
- dbatools 1.0.52
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
- Left click Start
- Microsoft SQL Server Tools
- Microsoft SQL Server Management Studio
Or alternatively, as the SQL Server Tools path will be appended to your users %PATH% variable:
- Right click Start
- Enter ssms in 'Open'
Object Explorer will likely open automatically, but if it doesn't do the following:
- Object Explorer (or, just F8)
Now, we'll connect to the database engine.
- Database Engine…
- Server name: (in our example I'm connecting to a named instance call SQL2017 on my local machine, so the full name of the SQL Server is .\SQL2017)
- Authentication (presuming you're using Active Directory authentication)
The name of the database we're going to take offline is called MyDatabase.
- Expand server dropdown
- Expand Databases dropdown
- Right click on database name - MyDatabase
- Take Offline
If the Status is 'Ready', there are no connections in the database.
- Check Status
But, if the status is 'Not Ready' as shown below.
- Click on the 'Message' link
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.
- Click 'New Query'
- Run EXEC sp_who2 in the query window
- F5 (or click Execute button)
- Look under the DBName column for any referenced to the database we're taking offline and note the corresponding number under the SPID column
Next, run kill with the spid on any that are in the database. Here we only have one with a spid = 57.
- Type in 'kill x' for each spid and highlight it
- F5 (or click Execute)
- Highlight 'EXEC sp_who2'
- Verify process has been killed
Go back to the Object Explorer.
- Right click on database name, MyDatabase
In the Take Database Offline window, do the following:
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).
If not, do the following:
- Right click 'Databases'
Take SQL Server Database Offline with T-SQL
We'll still use SSMS, but just the Query window.
- Right click on the server
- EXEC sp_who2 in the query window
Next, run the kill command with the spid on any that are in the database. Here we only have one with a spid = 55.
- F5 (or Execute)
Take the database offline with the following T-SQL:
- Highlight the statements
We didn't get any errors, but here's one way to verify the database is offline:
- Find the database name
- Check the state_desc column for status
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.
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.
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.
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.
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:
- Methods to determine the status of a SQL Server database
- Identify SQL Server databases that are no longer in use
- SQL Server Database Decommissioning Check List
- Retrieving SQL Server Database Properties with DATABASEPROPERTYEX
- Retrieve a List of SQL Server Databases and their Properties using PowerShell
- SQL Server Management Studio Overview (SSMS)
Related Articles
Popular articles.
About the author
Comments For This Article
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
- Login to SQL Server Management Studio.
- In the Object Explorer, select the database you want to take offline and right-click .
- The database will now taken offline.
NOTE: Follow the steps in this article to bring back the database online.
- Comparison between emergency mode, offline mode and detaching a database .
- Reference about ALTER DATABASE statement in Microsoft docs.
- Click to share on LinkedIn (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to share on WhatsApp (Opens in new window)
- Click to share on Pocket (Opens in new window)
- Click to email a link to a friend (Opens in new window)
- Click to print (Opens in new window)
You may also like...
Leave your thoughts... cancel reply.
This site uses Akismet to reduce spam. Learn how your comment data is processed .
IMAGES
VIDEO
COMMENTS
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.
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
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
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.