How to Take SQL Server Database Offline

Can't take SQL Server database offline because a lock could not be placed on database.
by Updated May 5, 2012

UPDATE 12-16-2011: If you are using ASP.NET and your website / database is live, and you want to take the database offline, you're better off putting an App_Offline.htm file in your website main directory to first take the website down for maintance (I believe this should also close any active connections to the database).  You should then be able to take your SQL Server database offline by simply using SQL Server Management Studio, rather than the code below. (END)

I ran into a problem earlier today, where I wasn't able to take a SQL Server database offline.  When I tried to take the database offline I would get the following error:

ALTER DATABASE failed because a lock could not be placed on database

To get around this error and bring the database offline, I used the following SQL query to bring it offline (just change "YourDatabaseName" with your database name):

USE master 
GO
 
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE 
GO

You can then bring the database back online, using:

USE master
GO

ALTER DATABASE YourDatabaseName SET MULTI_USER
GO

ALTER DATABASE YourDatabaseName SET ONLINE WITH ROLLBACK IMMEDIATE
GO

NOTE: Make sure that you reset your database back to SET MULTI_USER in a production environment (in other words, don't leave the database in SINGLE_USER mode.  Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions. If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

You can also change or check the database user Mode using SQL Server Management Studio, here's how:

To set a database to single-user mode or multi-user mode in Management Studio:

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  2. Right-click the database to change, and then click Properties.
  3. In the Database Properties dialog box, click the Options page.
  4. From the Restrict Access option, select SINGLE_USER (or MULTI_USER) 
  5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

 

Keep in mind that if you try the SET OFFLINE sql script above and still have issues geting your database offline, you may just want to do a quick "Stop" and "Start" (or "Restart") of your SQL Server to drop connections to the database so that you can take it offline.  To Stop and Start SQL Server, you will need to go into Computer Management -> Services, here's how:

  1. In Windows 7 (or Windows Server), click Start -> then in the search box type: "Computer Management"
  2. Open Computer Management -> then in the sidebar expand "Services and Applications" -> then click on "Services"
  3. In the Services window, scroll down until you find "SQL Server (MSSQLSERVER)" and select it.  If you using SQL Server Express you will select "SQL Server (SQL EXPRESS)"
  4. Now click "Stop the service" or "Restart the service".   If you click Stop the service, you will manually have to click "Start the service" after it has stopped to get SQL Server up and running.
  5. Once you've stopped or restarted SQL Server, all the previous connections and/ or locks should be gone so you can then Take the database offline.
  6. That's it.
 


0
0

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


In SQL Server 2008 Management Studio, you may want to change the default settings to allow you to edit more than the 200 rows at a time, or select more than 1000 rows, which are the default settings for SQL Server. To modify "Edit Top 200 Rows" or "Select...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »

Today I ran into the a "Restore failed for Server" error message while trying to restore a SQL Server 2008 database using a database backup (.bak) file. Below I will show you how to fix this restore error, so that you can restore your SQL Server database...  more »

I recently installed SQL Server 2008 database on my local computer and forgot to enable the common language runtime (CLR). Because I had forgotten to turn it on, some portions of my sites were not fuctioning properly due SQL Server not being able to...  more »

When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

Are you backing up your SQL Server 2008 databases daily? You should be, especially if you don't want to lose any of your precious data that you're storing. It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or...  more »