I need to restart a database because some processes are not working. My plan is to take it offline and back online again.
I am trying to do this in Sql Server Management Studio 2008:
use master;
go
alter database qcvalues
set single_user
with rollback immediate;
alter database qcvalues
set multi_user;
go
I am getting these errors:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
What am I doing wrong?
nalply
28.9k15 gold badges83 silver badges104 bronze badges
asked Jan 12, 2011 at 19:23
4
After you get the error, run
EXEC sp_who2
Look for the database in the list. It’s possible that a connection was not terminated. If you find any connections to the database, run
KILL <SPID>
where <SPID> is the SPID for the sessions that are connected to the database.
Try your script after all connections to the database are removed.
Unfortunately, I don’t have a reason why you’re seeing the problem, but here is a link that shows that the problem has occurred elsewhere.
http://www.geakeit.co.uk/2010/12/11/sql-take-offline-fails-alter-database-failed-because-a-lock-could-not-error-5061/
answered Jan 12, 2011 at 19:50
bobsbobs
22.2k12 gold badges69 silver badges80 bronze badges
5
I managed to reproduce this error by doing the following.
Connection 1 (leave running for a couple of minutes)
CREATE DATABASE TESTING123
GO
USE TESTING123;
SELECT NEWID() AS X INTO FOO
FROM sys.objects s1,sys.objects s2,sys.objects s3,sys.objects s4 ,sys.objects s5 ,sys.objects s6
Connections 2 and 3
set lock_timeout 5;
ALTER DATABASE TESTING123 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
answered Jan 12, 2011 at 20:11
Martin SmithMartin Smith
455k96 gold badges773 silver badges876 bronze badges
I will add this here in case someone will be as lucky as me.
When reviewing the sp_who2 list of processes note the processes that run not only for the effected database but also for master. In my case the issue that was blocking the database was related to a stored procedure that started a xp_cmdshell.
Check if you have any processes in KILL/RollBack state for master database
SELECT *
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'
If you have the same issue, just the KILL command will probably not help.
You can restarted the SQL server, or better way is to find the cmd.exe under windows processes on SQL server OS and kill it.
answered Jan 8, 2018 at 14:10
AlinaAlina
614 bronze badges
Just to add my two cents. I’ve put myself into the same situation, while searching the minimum required privileges of a db login to run successfully the statement:
ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
It seems that the ALTER statement completes successfully, when executed with a sysadmin login, but it requires the connections cleanup part, when executed under a login which has «only» limited permissions like:
ALTER ANY DATABASE
P.S. I’ve spent hours trying to figure out why the «ALTER DATABASE..» does not work when executed under a login that has dbcreator role + ALTER ANY DATABASE privileges. Here’s my MSDN thread!
answered Jul 20, 2015 at 17:00
In SQL Management Studio, go to Security -> Logins and double click your Login. Choose Server Roles from the left column, and verify that sysadmin is checked.
In my case, I was logged in on an account without that privilege.
HTH!
answered Dec 28, 2012 at 15:25
MartyMarty
9426 silver badges8 bronze badges
1
Killing the process ID worked nicely for me.
When running «EXEC sp_who2» Command over a new query window… and filter the results for the «busy» database , Killing the processes with «KILL » command managed to do the trick. After that all worked again.
aaaidan
7,3468 gold badges68 silver badges107 bronze badges
answered Jun 27, 2014 at 22:43
0
I know this is an old post but I recently ran into a very similar problem. Unfortunately I wasn’t able to use any of the alter database commands because an exclusive lock couldn’t be placed. But I was never able to find an open connection to the db. I eventually had to forcefully delete the health state of the database to force it into a restoring state instead of in recovery.
answered Feb 9, 2017 at 19:04
Geoff DawdyGeoff Dawdy
9167 gold badges16 silver badges43 bronze badges
In rare cases (e.g., after a heavy transaction is commited) a running CHECKPOINT system process holding a FILE lock on the database file prevents transition to MULTI_USER mode.
answered Aug 14, 2018 at 12:52
mitixmitix
3043 silver badges9 bronze badges
In my scenario, there was no process blocking the database under sp_who2. However, we discovered because the database is much larger than our other databases that pending processes were still running which is why the database under the availability group still displayed as red/offline after we tried to ‘resume data’by right clicking the paused database.
To check if you still have processes running just execute this command:
select percent complete from sys.dm_exec_requests
where percent_complete > 0
answered May 31, 2019 at 14:12
Start asking to get answers
Find the answer to your question by asking.
Ask question
Explore related questions
See similar questions with these tags.
Skip to content
Problem statement
As a DBA, you will be facing the error message “ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.” often for certain actions like taking database offline/setting single user etc.
Analysis
This is due to other processes locking your current operations.
Resolution
1. Find out the query which is blocking the current operation using the below query
Live monitoring query
2. Kill the process and take the database in SINGLE_USER
KILL 191 --session id from the first query related to the database
USE MASTER
ALTER DATABASE DB905 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
3. Take the database offline
ALTER DATABASE db905 SET OFFLINE WITH ROLLBACK IMMEDIATE
This site discusses and shares thoughts and experiences in SQL Server.
При работе с базами данных в Microsoft SQL Server Management Studio можно столкнуться с ситуацией, когда процедура перевода базы данных в Offline затягивается на очень длительное время…
В ряде случаев причиной такого поведения являются активные соединения с базой данных. Во многих источниках на такой случай можно найти рекомендацию перевода в состояние Offline с помощью инструкции WITH ROLLBACK IMMEDIATE, которая должна закрыть все активные соединения …
USE master
GO
ALTER DATABASE [Имя базы данных] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
Однако такой метод не всегда помогает, и в результате мы можем получить ошибку, говорящую о проблеме блокировки базы данных…
Чтобы получить информацию об активных сессиях с интересующей нас базой данных выполним SQL-запрос:
USE master
GO
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('Имя базы данных')
GO
Из полученного вывода станет понятно, – какой именно пользователь и из какого приложения “держит” нашу базу данных. Одним из интересующих нас показателей будет идентификатор соединения — spid
Помимо представленного примера информацию о всех активных соединениях к серверу БД можно получить с помощью хранимых процедур sp_who или sp_who2:
EXEC sp_who2
Выяснив идентификатор соединения можно его “прибить” командой:
KILL <spid>
После сброса активных сессий интересующая нас база данных должна успешно завершить переход состояние Offline.
Пройдите тест, узнайте какой профессии подходите
Работать самостоятельно и не зависеть от других
Работать в команде и рассчитывать на помощь коллег
Организовывать и контролировать процесс работы
Быстрый ответ
Для устранения проблемы с отсутствием возможности блокировки при использовании оператора ALTER DATABASE, нужно прекратить активные сессии и переключить режим работы базы данных в SINGLE_USER:
- Завершите активные сессии:
-
Выполните нужную команду ALTER DATABASE.
-
Верните обычный доступ:
Примечание: Замените ИмяВашейБазыДанных на название вашей базы данных. Вышеуказанный метод принудительно закрывает все соединения, устраняя все препятствия для блокировки.
Активные соединения: ещё одна проблема?
Активные соединения могут серьёзно затруднить выполнение операций ALTER DATABASE.
Активные соединения и вы: выявление проблем
Прежде чем принимать меры, определите причину проблемы:
- Запустите
sp_who2, чтобы узнать об активных сессиях:
- Отфильтруйте результаты, чтобы видеть только соединения с нужной вам базой данных.
Столкновение с активными соединениями: грамотное завершение
Для приостановки сессий без лишней паники:
- Оцените последствия использования
WITH ROLLBACK IMMEDIATEи применяйте команду обоснованно, так как она откатывает текущие транзакции. - Используйте более изощрённый метод с
KILL <SPID>, чтобы приостановить сессии по одной. - Обязательно убедитесь, что у вас есть права
sysadminилиdbcreator, так как для реализации этих действий требуются соответствующие полномочия.
Предотвращение новых подключений: избегайте нежданчиков
Очистив «поле битвы», не дайте возникнуть новым «боевикам»:
- Не приступайте к работе с базой данных в время выполнения операции.
- Держите базу данных в режиме SINGLE_USER в течение всего времени операции ALTER DATABASE.
Урегулирование ситуации: устранение корневых проблем
Частые блокировки могут указывать на другие, более глубоко укоренённые проблемы в системе:
Власть доступа: проверка полномочий пользователей
Убедитесь, что у вашего аккаунта есть достаточно прав и ролей для выполнения задач.
Скрытый противник: увеличение журнала транзакций
Неудержимый рост журнала транзакций может свести на нет работу базы данных. Регулярная резервная копия поможет держать ситуацию под контролем.
Медлительные и упорные: долгосрочные запросы
Тяжёлые запросы могут блокировать операции ALTER DATABASE:
- Используйте SQL Profiler для обнаружения и анализа застрявших транзакций.
- Проведите оптимизацию «тяжёлых» запросов, обновляя индексы или пересматривая их структуру.
Визуализация
Представьте себя, что база данных — это ваш личный сейф (🔁🔒💼).
Вы намерены провести ремонт внутри сейфа:
Цель — переключиться с прямого столкновения на более утончённое решение проблемы блокировки:
Цель: чтобы провести изменения в сейфе (💼), вы должны решить проблему с блокировкой (🔐) или дождаться момента, когда она не будет активна.
Предупредительные стратегии и запасные решения
Превентивная профилактика проблем
Не нужно спешить с принятием решений:
- Проверьте настройки, например, timeout-ы для блокировок, чтобы избежать неприятных ситуаций.
- Планируйте изменения так, чтобы минимизировать неудобства для пользователей.
Всегда на чеку
Следите за состоянием своей системы:
- Используйте инструменты мониторинга для оперативного обнаружения конфликтов за ресурсы или необычной активности в системе.
- Регулярно проводите диагностику системы, чтобы избежать возникновения проблем.
План «Б»: варианты в случае аварийной ситуации
Если мирные решения не спасают:
- Перезагрузить SQL Server может быть последним вариантом.
- Рассмотрите возможность создания и использования новой базы данных, если конфликтов с текущей базой становится слишком много.
Полезные материалы
- ALTER DATABASE (Transact-SQL) – SQL Server | Microsoft Learn — Исследуйте особенности работы с оператором ALTER DATABASE и связанным с ним проблемам блокировок.
- How to identify blocking in SQL Server — Детальное руководство по определению и решению проблем блокировки в SQL Server.
- Transactions (Transact-SQL) – SQL Server | Microsoft Learn — Документация по использованию транзакций в SQL Server, которая может быть полезна при разрешении проблем с операцией.
- How to Identify Blocking Problems with SQL Profiler – Simple Talk — Советы по использованию SQL Profiler для выявления и анализа проблем с блокировкой.
Every consulting engagement is different and I get to learn new things. In this blog post, I will share what I learned during my recent Comprehensive Database Performance Health Check. During health check to demonstrate one of the scenarios we had created a test database in our environment. However, after the test was complete we wanted to take the database offline so we eventually can drop it or bring it online if needed, however, as soon as we ran the command to take database offline, it gave is an error related to alter database failed because a lock could not be placed on database.
If you want to learn how to take the database offline and online, you can read my earlier blog post here: SQL SERVER – T-SQL Script to Take Database Offline – Take Database Online.
If you try to take your database offline and you see the following error:
Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'database name'. Try again later. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
SOLUTION/WORKAROUND
In that case, you run first the following command try to find your database in the list. It is totally possible due to some reason, your database connection is still active and not terminated.
EXEC sp_who2
If you are comfortable you can easily kill the SPID by running following command.
After you get the error, run
KILL (SPID)
Once you kill all the active sessions you can again run the command specified in the blog post and take your database offline using alter database command.
Reference: Pinal Dave (https://blog.sqlauthority.com)