Visual Studio 데이터베이스 프로젝트에서 자주 배포하는 개발 데이터베이스가 있습니다 (TFS 자동 빌드를 통해).
때로는 빌드를 실행할 때 다음 오류가 발생합니다.
ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.
ALTER DATABASE statement failed.
Cannot drop database "MyDB" because it is currently in use. 나는 이것을 시도했다 :
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE하지만 여전히 데이터베이스를 삭제할 수 없습니다. (제 생각에는 대부분의 개발자가 dbo액세스 할 수 있습니다.)
SP_WHO연결을 수동으로 실행 하고 종료 할 수 있지만 자동 빌드에서이를 수행하는 자동 방법이 필요합니다. (이번에도 내 연결은 삭제하려는 DB의 유일한 연결입니다.)
누가 연결되어 있는지에 관계없이 데이터베이스를 삭제할 수있는 스크립트가 있습니까?
답변
업데이트
MS SQL Server 2012 이상
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('MyDB')
EXEC(@kill);MS SQL Server 2000, 2005, 2008
USE master;
DECLARE @kill varchar(8000); SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')
EXEC(@kill); 답변
USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO참조 : http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx
답변
다음을 수행하여 SSMS가 제공하는 스크립트를 얻을 수 있습니다.
- SSMS에서 데이터베이스를 마우스 오른쪽 버튼으로 클릭하고 삭제를 선택하십시오.
- 대화 상자에서 “기존 연결 닫기”확인란을 선택하십시오.
- 대화 상자 상단의 스크립트 버튼을 클릭하십시오.
스크립트는 다음과 같습니다.
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO답변
거의 알려지지 않음 : GO sql 문은 이전 명령을 반복하는 데 여러 번 정수를 사용할 수 있습니다.
따라서 당신이 :
ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO그때:
USE [DATABASENAME]
GO 2000USE 명령을 2000 번 반복하고 다른 모든 연결에서 교착 상태를 강제 실행하고 단일 연결의 소유권을 갖습니다. (원하는대로 쿼리 창에 단독 액세스 권한을 부여하십시오.)
답변
내 경험상 SINGLE_USER를 사용하면 대부분 도움이되지만 조심해야합니다 .SINGLE_USER 명령을 시작한 시간과 완료 된 시간 사이에 다른 ‘사용자’가 생겼습니다. 내가 아닌 SINGLE_USER 액세스 이런 일이 발생하면 데이터베이스에 다시 액세스하려고하는 힘든 일을하고 있습니다 (제 경우에는 SINGLE_USER 액세스를 보유한 SQL 데이터베이스가있는 소프트웨어에 대해 특정 서비스가 실행 중이었습니다). 가장 신뢰할 수있는 방법이어야한다고 생각합니다 (보증 할 수는 없지만 앞으로 테스트 할 것입니다). 실제로는 다음과 같습니다
– 액세스를 방해 할 수 있습니다 스톱 서비스 (어떤이있는 경우)
– 위의 ‘kill’스크립트를 사용하여 모든 연결을 닫으십시오
-그 직후 데이터베이스를 single_user로 설정
한 다음 복원을 수행하십시오.
답변
Matthew의 가장 효율적인 스크립트는 dm_exec_sessions DMV를 사용하도록 업데이트되어 더 이상 사용되지 않는 sysprocesses 시스템 테이블을 대체합니다.
USE [master];
GO
DECLARE @Kill VARCHAR(8000) = '';
SELECT
@Kill = @Kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM
sys.dm_exec_sessions
WHERE
database_id = DB_ID('<YourDB>');
EXEC sys.sp_executesql @Kill;WHILE 루프를 사용하는 대안 (실행마다 다른 작업을 처리하려는 경우) :
USE [master];
GO
DECLARE @DatabaseID SMALLINT = DB_ID(N'<YourDB>');
DECLARE @SQL NVARCHAR(10);
WHILE EXISTS ( SELECT
1
FROM
sys.dm_exec_sessions
WHERE
database_id = @DatabaseID )
BEGIN;
SET @SQL = (
SELECT TOP 1
N'kill ' + CAST(session_id AS NVARCHAR(5)) + ';'
FROM
sys.dm_exec_sessions
WHERE
database_id = @DatabaseID
);
EXEC sys.sp_executesql @SQL;
END;답변
허용되는 답변에는 연결된 데이터베이스 이외의 데이터베이스에있는 테이블이 포함 된 쿼리를 실행하는 연결에 의해 데이터베이스를 잠글 수 없다는 단점이 있습니다.
서버 인스턴스에 둘 이상의 데이터베이스가 있고 쿼리가 직접 또는 간접적으로 (예를 들어 동의어를 통해) 둘 이상의 데이터베이스에서 테이블을 사용하는 경우에 해당됩니다.
따라서 때때로 syslockinfo를 사용하여 종료 할 연결을 찾는 것이 더 낫다는 것을 알았습니다.
따라서 내 제안은 AlexK의 허용되는 답변을 다음과 같이 변형하는 것입니다.
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), req_spid) + ';'
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid = db_id('MyDB')
EXEC(@kill);