데이터베이스에 대한 모든 연결을 끊는 스크립트(RESTRICTED_USER Rollback 이상)
Visual Studio Database 프로젝트에서 (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에서 데이터베이스를 마우스 오른쪽 버튼으로 클릭하고 delete를 선택합니다.
- 대화상자에서 "기존 연결 닫기" 확인란을 선택합니다.
- 대화상자 상단에 있는 [스크립트]버튼을 클릭합니다.
스크립트는 다음과 같습니다.
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 2000
그러면 USE 명령어가 2000회 반복되고 다른 모든 연결에서 데드록이 강제로 실행되며 단일 연결의 소유권을 획득합니다(쿼리 창에만 원하는 액세스 권한을 부여합니다).
으로는 SINGLE_USER를 해야 합니다.SINGLE_USER는 SINGLE_USER입니다.SINGLE_USER는 내가 것 .내가 아닌 다른 '사용자'가 SINGLE_USER 접근권을 가지고 있었던 것 같습니다.이 경우 데이터베이스에 대한 접근을 되돌리려면 어려운 작업이 필요합니다(이 경우 SQL 데이터베이스를 가진 소프트웨어에서 실행 중인 특정 서비스가 먼저 SINGLE_USER 액세스를 확보했습니다).가장 신뢰할 수 있는 방법(보증할 수는 없지만 앞으로 테스트해 보겠습니다)은 실제로 다음과 같습니다.
수 있는 경우 - "서비스 중지"
- 위의 'kill' 스크립트를 사용하여 모든 연결을 닫습니다.
- 그 직후에 데이터베이스를 single_user로 설정합니다.
- restore를 실행합니다.
Matthew의 매우 효율적인 스크립트는 dm_exec_sessions DMV를 사용하도록 업데이트되어 사용되지 않는 syspprocesses 시스템 테이블을 대체합니다.
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;
WHY 루프를 사용하는 대체 방법(실행별로 다른 작업을 처리하는 경우):
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);
살인 과정에서는 예외에 주의해야 합니다.따라서 다음 스크립트를 사용할 수 있습니다.
USE master;
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;' FROM master..sysprocesses
EXEC (@kill)
@AlexK는 훌륭한 답변을 썼다.나는 단지 내 의견을 덧붙이고 싶다.아래 코드는 전적으로 @AlexK의 답변에 기초하고 있습니다.차이점은 사용자와 마지막 배치가 실행된 이후의 시간을 지정할 수 있다는 것입니다(코드는 마스터 대신 sys.dm_exec_display를 사용합니다).syspprocess) :
DECLARE @kill varchar(8000);
set @kill =''
select @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' from sys.dm_exec_sessions
where login_name = 'usrDBTest'
and datediff(hh,login_time,getdate()) > 1
--and session_id in (311,266)
exec(@kill)
이 예에서는 사용자 usrDB 프로세스만마지막 배지가 1시간 이상 전에 실행된 테스트는 중지됩니다.
다음과 같이 커서를 사용할 수 있습니다.
USE master
GO
DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'
DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database
OPEN Murderer
FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
EXEC (@SQL)
PRINT ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
FETCH NEXT FROM Murderer INTO @SPID
END
CLOSE Murderer
DEALLOCATE Murderer
블로그에 써놨어요.http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
SELECT
spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
memusage Memory,
physical_io DiskIO,
lastwaittype LastWaitType,
[program_name] ProgramName,
last_batch LastBatch,
login_time LoginTime,
'kill ' + CAST(spid as varchar(10)) as 'Kill Command'
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb')
--AND sd.name = 'db_name'
--AND hostname like 'hostname1%'
--AND loginame like 'username1%'
ORDER BY spid
/* If a service connects continously. You can automatically execute kill process then run your script:
DECLARE @sqlcommand nvarchar (500)
SELECT @sqlcommand = 'kill ' + CAST(spid as varchar(10))
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb')
--AND sd.name = 'db_name'
--AND hostname like 'hostname1%'
--AND loginame like 'username1%'
--SELECT @sqlcommand
EXEC sp_executesql @sqlcommand
*/
USE MASTER
GO
DECLARE @Spid INT
DECLARE @ExecSQL VARCHAR(255)
DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT SPID
FROM MASTER..SysProcesses
WHERE DBID = DB_ID('dbname')
OPEN KillCursor
-- Grab the first SPID
FETCH NEXT
FROM KillCursor
INTO @Spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50))
EXEC (@ExecSQL)
-- Pull the next SPID
FETCH NEXT
FROM KillCursor
INTO @Spid
END
CLOSE KillCursor
DEALLOCATE KillCursor
데이터베이스만 삭제/삭제하려면 기본적으로 설정되지 않은 "기존 연결 닫기" 옵션을 선택할 수 있습니다.
- [ Database catalog - ]-> 옵션을 오른쪽 클릭합니다.
- [ Delete - > ]체크박스를 켜겠습니다
- 네 알겠습니다
아래의 간단한 코드로 테스트에 성공했습니다.
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
언급URL : https://stackoverflow.com/questions/7197574/script-to-kill-all-connections-to-a-database-more-than-restricted-user-rollback
'source' 카테고리의 다른 글
| 팀에 대한 iTunes Connect 액세스 권한이 있는 계정을 찾을 수 없습니다. (0) | 2023.04.17 |
|---|---|
| 도커 이미지의 새 컨테이너에서 bash를 실행하려면 어떻게 해야 합니까? (0) | 2023.04.17 |
| 실행 중인 cmdlet의 현재 디렉토리를 가져오는 방법 (0) | 2023.04.17 |
| bash에서 바이너리로 파일을 표시하는 방법 (0) | 2023.04.17 |
| UNIX의 셸 스크립트를 사용하여 10일 이상 지난 파일 삭제 (0) | 2023.04.17 |