locked
Bring Database Online/Offline RRS feed

  • Question

  • In SQL Server 2005, I tried to take a database offline by selecting "take offline."  My computer had a problem during this now I get the following error...

    ALTER DATABASE failed because a lock could not be placed on database 'CJISData'. Try again later.
    ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)

    I can't bring the database online or take it offline.  I am not sure where to go from here.  Any help will be appreciated.  Thanks.

    Bernie

    Monday, February 12, 2007 3:39 PM

Answers

  • Make sure you drop all connections on the database first or use the WITH ROLLBACK IMMEDIATE.

    HTH; Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Monday, February 12, 2007 6:18 PM
  • Trying doing this first

    USE [master]

    GO

    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

     

    And do remember to make the database MULTI_USER after the restore.

     

    USE [master]

    GO

    ALTER DATABASE <dbname> SET MULTI_USER

    GO

     

    I think this will solve your problem

    Monday, September 22, 2008 5:24 AM

All replies

  • Make sure you drop all connections on the database first or use the WITH ROLLBACK IMMEDIATE.

    HTH; Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Monday, February 12, 2007 6:18 PM
  • I'm having the same problem, even when using WITH ROLLBACK IMMEDIATE. I trying this:
    alter database <dbname> set offline with rollback immediate

    This doesn't seem to drop the connections to the database first. It just fails. When I check the connections, I see that the application server (weblogic) still is connected.

    My solution this far, has been to detach the database, and keep it detached for a little while, until weblogic stops trying to reconnect.

    Guess I'll end up with a script solution that kills all the processes that connects to the database. Probably a loop that will

      select @SPId = min(SPId) from master..SysProcesses where DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

    and then

    EXECUTE('kill ' + @SPId)

    Kjetil
    Tuesday, September 16, 2008 2:04 PM
  • Trying doing this first

    USE [master]

    GO

    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

     

    And do remember to make the database MULTI_USER after the restore.

     

    USE [master]

    GO

    ALTER DATABASE <dbname> SET MULTI_USER

    GO

     

    I think this will solve your problem

    Monday, September 22, 2008 5:24 AM
  • Thanks Kunal, this helps me..

     

    Friday, October 31, 2008 9:19 PM
  • I also run in to the same issue.

    Restarting the SQL server service and running the following script helped:

    ALTER

    DATABASE %Database_Name% SET OFFLINE WITH ROLLBACK IMMEDIATE

    To bring the database back online, just use:

    ALTER DATABASE %Database_Name% SET ONLINE WITH ROLLBACK IMMEDIATE

    Wednesday, April 15, 2009 7:02 PM
  • I did a sp_who2. Found the culprit. Asked them to get out. And then the database went offline.
    physics is phun ...
    Wednesday, April 29, 2009 4:51 AM
  • Thanks for this post, I wrote up my solution here...

    It looks as though the single user has some kind of sleeping lock on the dB which needs to be killed before it can be taken offline.


    Saturday, December 11, 2010 11:47 AM
  • Can you please write your solution here??
    Harry
    Monday, September 12, 2011 2:42 AM
  • Hi,

    This is a good solution. It worked well. Use sp_lock to find the spid's which are holding locks on the DB and then kill them. The DB will then come to offline. 

    • Proposed as answer by PGChowdary Wednesday, November 9, 2011 2:17 PM
    Wednesday, November 9, 2011 2:17 PM
  • I couldn't get the database into single user mode. I continued to get a lock couldn't be placed on the database. I was finally able to place my database into an offline status. I had to do a combination of the suggestions above. I detached the database. I then re-attached the database and then executed the commands below

    alter database %Database Name%
    set single_user with rollback immediate


    alter database %Database Name%
    set multi_user


    alter database %Database Name%
    set offline
    • Proposed as answer by Serpentdove Friday, March 2, 2012 8:24 AM
    • Unproposed as answer by Serpentdove Friday, March 2, 2012 8:25 AM
    Thursday, December 8, 2011 7:39 PM
  • Just restart the SQLSERVER services...
    Friday, March 2, 2012 8:26 AM
  • This could be because a specific process has locked the DB.

    I will suggest you do the following:

    (1) Run the Sql Command (New Sql Query)

    EXEC sp_who2 

    (2) Identify who is using the Particular DB by searching the DB name in the DBName column

    (3) If you consider is safe and appropriate, you can kill the process/activity (Identify the SPID number and replace it below)

    KILL <SPID>

    Hope it help

    • Proposed as answer by JoseTru Thursday, March 22, 2012 1:28 AM
    Thursday, March 22, 2012 1:27 AM