Can not put database in offline mode because a lock could not be placed on database
Question
Monday, September 12, 2011 1:58 AM | 1 vote
hi,
One annoying problem has occurred....I want to put database in offline mode....but it is giving me some error...
"ALTER DATABASE failed because a lock could not be placed on database [database]. Try again later. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)"
Harry
All replies (7)
Monday, September 12, 2011 3:56 AM âś…Answered | 2 votes
Harry,
Please check for open transactions on that database that are holding locks. You may use the following query as I posted here.
select * from sys.dm_tran_locks where resource_database_id=YourDatabaseID
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @PradeepAdiga
Monday, September 12, 2011 2:11 AM | 1 vote
Hi Harry,
You can use ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE statement. See more details in MSDN
Thank you!
My blog: http://aboutsqlserver.com
Monday, September 12, 2011 2:12 AM
Dmitri,
I already tried this......but still it did not work.....
Harry
Monday, September 12, 2011 2:15 AM
I even restarted SQL Servrer service......but it did not work....
Harry
Monday, September 12, 2011 2:46 AM | 1 vote
As the work around - try to set database to the single_user mode first.
Thank you!
My blog: http://aboutsqlserver.com
Wednesday, October 14, 2015 7:46 PM
Thanks for help ... this information very good..
Wednesday, November 11, 2015 7:22 AM
Hi Harry ,
In mine case lock is acquired by background process which is DBstartup , What should be done in this case . Should I kill that process as well or should wait as it is a system process .
Thanks.