Скрипт для уничтожения всех подключений к базе данных (более чем ограниченный откат пользователя)

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Ref: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 раз, принудительно заблокирует все другие соединения и возьмет на себя ответственность за одно соединение. (Предоставление единственного доступа к окну запроса, чтобы сделать так, как вы хотите.)

по моему опыту, использование SINGLE_USER помогает в большинстве случаев, однако, следует быть осторожным: я испытал случаи, в которых между временем запуска команды SINGLE_USER и временем ее завершения... очевидно, другой "пользователь" получил доступ к SINGLE_USER, а не я. Если это произойдет, вам предстоит сложная работа по возвращению доступа к базе данных (в моем случае это была конкретная служба, работающая для программного обеспечения с базами данных SQL, которые получили доступ к SINGLE_USER доступ до того, как я сделал). То, что я думаю, должно быть самым надежным способом (не могу ручаться за это, но это то, что я проверю в ближайшие дни), на самом деле: - остановить службы, которые могут помешать вашему доступу (если таковые имеются) - используйте скрипт "убить" выше, чтобы закрыть все соединения - установите базу данных в single_user сразу после этого - тогда сделайте восстановление

Как это звучит ?

чрезвычайно эффективный скрипт Мэтью обновлен для использования 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;

вы должны быть осторожны с исключениями во время процессов убийства. Так что вы можете использовать этот скрипт:

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_sessions вместо 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)

в этом примере будет убит только процесс пользователя usrDBTest, последний пакет которого был выполнен более 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
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO