데이터베이스에 대한 모든 연결을 종료하는 스크립트 (RESTTEDTED_USER ROLLBACK 이상)

데이터베이스에 대한 모든 연결을 종료하는 스크립트 (RESTTEDTED_USER ROLLBACK 이상)

---

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가 제공하는 스크립트를 얻을 수 있습니다.

1. SSMS에서 데이터베이스를 마우스 오른쪽 버튼으로 클릭하고 삭제를 선택하십시오.
2. 대화 상자에서 "기존 연결 닫기"확인란을 선택하십시오.
3. 대화 상자의 상단에있는 스크립트 버튼을 클릭하십시오.

스크립트는 다음과 같습니다.

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 번 반복하고 다른 모든 연결에서 교착 상태를 강제 실행하고 단일 연결의 소유권을 갖습니다. (원하는대로 쿼리 창에 단독 액세스 권한을 부여하십시오.)

---

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;

---

To my experience, using SINGLE_USER helps most of the times, however, one should be careful: I have experienced occasions in which between the time I start the SINGLE_USER command and the time it is finished... apparently another 'user' had gotten the SINGLE_USER access, not me. If that happens, you're in for a tough job trying to get the access to the database back (in my case, it was a specific service running for a software with SQL databases that got hold of the SINGLE_USER access before I did). What I think should be the most reliable way (can't vouch for it, but it is what I will test in the days to come), is actually:
- stop services that may interfere with your access (if there are any)
- use the 'kill' script above to close all connections
- set the database to single_user immediately after that
- then do the restore

---

You should be careful about exceptions during killing processes. So you may use this script:

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 wrote a great answer. I just want to add my two cents. The code below is entirely based on @AlexK's answer, the difference is that you can specify the user and a time since the last batch was executed (note that the code uses sys.dm_exec_sessions instead of master..sysprocess):

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)

In this example only the process of the user usrDBTest which the last batch was executed more than 1 hour ago will be killed.

---

You can use Cursor like that:

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

I wrote about that in my blog here: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor

---

The accepted answer has the drawback that it doesn't take into consideration that a database can be locked by a connection that is executing a query that involves tables in a database other than the one connected to.

This can be the case if the server instance has more than one database and the query directly or indirectly (for example through synonyms) use tables in more than one database etc.

I therefore find that it sometimes is better to use syslockinfo to find the connections to kill.

My suggestion would therefore be to use the below variation of the accepted answer from 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);

---

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

---

I have tested successfully with simple code below

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