[SQL] 스크립트가 데이터베이스에 모든 연결을 죽일 (더 RESTRICTED_USER ROLLBACK 이상)

스크립트가 데이터베이스에 모든 연결을 죽일 (더 RESTRICTED_USER ROLLBACK 이상)

나는 (A 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에있는 유일한 하나입니다.)

연결된 사용자의 내 데이터베이스에 관계없이 삭제할 수있는 스크립트가 있습니까?

해결법

1. ==============================

   1.업데이트

   업데이트

   위의 MS SQL 서버 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);
   

   2008 MS SQL 서버 2000, 2005의 경우

   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); 
   

2. ==============================

   2.

   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

3. ==============================

   3.당신은 SSMS 다음을 수행하여 제공하는 스크립트를 얻을 수 있습니다 :

   당신은 SSMS 다음을 수행하여 제공하는 스크립트를 얻을 수 있습니다 :

   스크립트는 다음과 같이 표시됩니다

   USE [master]
   GO
   ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
   GO
   USE [master]
   GO
   DROP DATABASE [YourDatabaseName]
   GO
   

4. ==============================

   4.작은 알려진 다음 GO SQL 문은 이전 명령을 반복 할 횟수에 대한 정수를 취할 수 있습니다.

   작은 알려진 다음 GO SQL 문은 이전 명령을 반복 할 횟수에 대한 정수를 취할 수 있습니다.

   당신이 경우에 따라서 :

   ALTER DATABASE [DATABASENAME] SET SINGLE_USER
   GO
   

   그때:

   USE [DATABASENAME]
   GO 2000
   

   이것은 다른 모든 연결에 사용 명령 2000 회, 힘의 교착 상태를 반복하고, 단일 연결의 소유권을 취할 것입니다. (당신이 원하는대로 할 쿼리 창 단독 액세스를 부여.)

5. ==============================

   5.내 경험에, SINGLE_USER를 사용하는 시간의 대부분은, 그러나, 하나는주의해야하는 데 도움이 : 나는 내가 SINGLE_USER 명령하고 종료 시간을 시작 시간 사이에있는 경험이 풍부한 경우가 ... 분명히 다른 '사용자'가 먹은 SINGLE_USER 액세스하지 나. 그렇게되면, 데이터베이스의 뒷면에 접근려고 힘든 작업에있어 (내 경우를, 내가 한 전에 SINGLE_USER 액세스 잡고있어 SQL 데이터베이스와 소프트웨어를 실행하는 특정 서비스이었다). 내가 생각하는 가장 신뢰할 수있는 방법이 (그것을 위해 보증 할 수 있지만, 내가 올 일에 테스트 할 것입니다), 실제로한다 : - (존재하는 경우) 액세스를 방해 할 수 있습니다 스톱 서비스 - 모든 연결을 닫습니다 위의 '죽'스크립트를 사용하여 - 직후 그 SINGLE_USER에 대한 데이터베이스를 설정 - 다음 복원 할

   내 경험에, SINGLE_USER를 사용하는 시간의 대부분은, 그러나, 하나는주의해야하는 데 도움이 : 나는 내가 SINGLE_USER 명령하고 종료 시간을 시작 시간 사이에있는 경험이 풍부한 경우가 ... 분명히 다른 '사용자'가 먹은 SINGLE_USER 액세스하지 나. 그렇게되면, 데이터베이스의 뒷면에 접근려고 힘든 작업에있어 (내 경우를, 내가 한 전에 SINGLE_USER 액세스 잡고있어 SQL 데이터베이스와 소프트웨어를 실행하는 특정 서비스이었다). 내가 생각하는 가장 신뢰할 수있는 방법이 (그것을 위해 보증 할 수 있지만, 내가 올 일에 테스트 할 것입니다), 실제로한다 : - (존재하는 경우) 액세스를 방해 할 수 있습니다 스톱 서비스 - 모든 연결을 닫습니다 위의 '죽'스크립트를 사용하여 - 직후 그 SINGLE_USER에 대한 데이터베이스를 설정 - 다음 복원 할

6. ==============================

   6.마태 복음의 굉장히 효율적인 스크립트는 사용되지 않는 sysprocesses 시스템 테이블을 대체하는 dm_exec_sessions DMV를 사용하도록 업데이트 :

   마태 복음의 굉장히 효율적인 스크립트는 사용되지 않는 sysprocesses 시스템 테이블을 대체하는 dm_exec_sessions DMV를 사용하도록 업데이트 :

   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;
   

7. ==============================

   7.허용 대답은 데이터베이스에 연결된 데이터베이스가 아닌 다른 데이터베이스에 테이블을 포함하는 쿼리를 실행하는 연결에 의해 고정 될 수는 고려하지 않는다는 단점이있다.

   허용 대답은 데이터베이스에 연결된 데이터베이스가 아닌 다른 데이터베이스에 테이블을 포함하는 쿼리를 실행하는 연결에 의해 고정 될 수는 고려하지 않는다는 단점이있다.

   서버 인스턴스가 하나 이상의 데이터베이스와 쿼리를 직접 또는 간접적으로 (동의어를 통해 예를 들어) 사용 테이블 하나 이상의 데이터베이스 등이 경우에 해당 될 수 있습니다

   그래서 저는 가끔 죽일 연결을 찾을 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);
   

8. ==============================

   8.당신은 프로세스 종료시 예외주의해야한다. 이 스크립트를 사용할 수 있도록 :

   당신은 프로세스 종료시 예외주의해야한다. 이 스크립트를 사용할 수 있도록 :

   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)
   

9. ==============================

   9.@AlexK은 훌륭한 대답을 썼다. 난 그냥 내 두 센트를 추가 할. 아래의 코드가 완전히 AlexK의 대답 @ 기반으로, 차이는 마지막 일괄 처리가 실행 된 이후에는 사용자와 시간을 지정할 수 있다는 것입니다 (참고 대신 master..sysprocess의 코드 사용의 바로 sys.dm_exec_sessions) :

   @AlexK은 훌륭한 대답을 썼다. 난 그냥 내 두 센트를 추가 할. 아래의 코드가 완전히 AlexK의 대답 @ 기반으로, 차이는 마지막 일괄 처리가 실행 된 이후에는 사용자와 시간을 지정할 수 있다는 것입니다 (참고 대신 master..sysprocess의 코드 사용의 바로 sys.dm_exec_sessions) :

   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)
   

   이 예에서 마지막 배치는 1 시간 이상 전에 실행 된 사용자 usrDBTest의 프로세스는 삭제됩니다.

10. ==============================

    10.당신은 그렇게 커서를 사용할 수 있습니다 :

    당신은 그렇게 커서를 사용할 수 있습니다 :

    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

11. ==============================

    11.

    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
    */
    

12. ==============================

    12.나는 아래의 간단한 코드로 성공적으로 테스트 한

    나는 아래의 간단한 코드로 성공적으로 테스트 한

    USE [master]
    GO
    ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    

from https://stackoverflow.com/questions/7197574/script-to-kill-all-connections-to-a-database-more-than-restricted-user-rollback by cc-by-sa and MIT license