Не удалось выполнить инструкцию alter database

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.

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?

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

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

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

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.

answered Feb 9, 2017 at 19:04

answered Aug 14, 2018 at 12:52

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