Skrypt zabijający wszystkie połączenia z bazą danych (więcej niż ograniczone wycofywanie użytkowników)
Mam bazę programistyczną, która często wdraża się z projektu bazy danych Visual Studio (poprzez TFS Auto Build).
Czasami kiedy uruchamiam mój build dostaję ten błąd:
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.
Próbowałem tego:
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
Ale nadal nie mogę porzucić bazy danych. (Domyślam się, że większość programistów ma dostęp dbo
.)
Mogę uruchomić ręcznie SP_WHO
i rozpocząć zabijanie połączeń, ale potrzebuję automatycznego sposobu, aby to zrobić w auto build. (Choć tym razem moje połączenie jest jedyny z db, który próbuję upuścić.)
Czy istnieje skrypt, który może upuścić moją bazę danych niezależnie od tego, kto jest podłączony?
11 answers
Aktualizacja
Dla MS SQL Server 2012 i nowszych
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);
Dla 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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-03 10:08:59
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-08-29 16:29:22
Skrypt SSMS można pobrać wykonując następujące czynności:
- Kliknij prawym przyciskiem myszy na bazie danych w SSMS i wybierz Usuń
- w oknie dialogowym zaznacz pole wyboru " zamknij istniejące połączenia."
- Kliknij przycisk skrypt u góry okna dialogowego.
Skrypt będzie wyglądał mniej więcej tak:
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-15 18:01:19
Mało znany: polecenie GO sql może przyjmować liczbę całkowitą, aby powtórzyć poprzednie polecenie.
Więc jeśli:
ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO
Wtedy:
USE [DATABASENAME]
GO 2000
To powtórzy polecenie użyj 2000 razy, wymusi zablokowanie wszystkich innych połączeń i przejmie własność pojedynczego połączenia. (Dając wyłączny dostęp do okna zapytania, aby zrobić, jak chcesz.)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-09-30 23:12:50
Z mojego doświadczenia wynika, że używanie SINGLE_USER pomaga w większości przypadków, Jednak należy być ostrożnym: doświadczyłem sytuacji, w których pomiędzy uruchomieniem polecenia SINGLE_USER a zakończeniem... widocznie inny "użytkownik" dostał dostęp do SINGLE_USER, nie ja. Jeśli tak się stanie, czeka cię ciężka praca, próbując odzyskać dostęp do bazy danych (w moim przypadku była to konkretna usługa uruchomiona dla oprogramowania z bazami danych SQL, które uzyskało dostęp SINGLE_USER zanim to zrobiłem). To, co moim zdaniem powinno być najbardziej wiarygodne (nie ręczę za to ,ale to, co przetestuję w najbliższych dniach), jest rzeczywiście: - zatrzymać usługi, które mogą zakłócać dostęp (jeśli są jakieś) - Użyj skryptu 'kill' powyżej, aby zamknąć wszystkie połączenia - Ustaw bazę danych na single_user natychmiast po tym - następnie wykonaj restore
Jak to brzmi ?
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-08-07 12:20:39
Niezwykle wydajny skrypt Matthew został zaktualizowany, aby używać DMV dm_exec_sessions, zastępując przestarzałą tabelę systemową 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;
Alternatywa użycia pętli WHILE (jeśli chcesz przetworzyć inne operacje na wykonanie):
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-18 15:22:47
Należy uważać na wyjątki podczas procesów zabijania. Możesz więc użyć tego skryptu:
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-16 23:31:37
@AlexK napisał świetną Odpowiedź . Chcę tylko dodać moje dwa centy. Poniższy kod jest całkowicie oparty na odpowiedzi @AlexK, różnica polega na tym, że możesz określić użytkownika i czas od ostatniego uruchomienia partii (zauważ, że kod używa sys. dm_exec_sessions zamiast 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)
W tym przykładzie zostanie zabity tylko proces użytkownika usrDBTest, którego ostatnia partia została wykonana ponad 1 godzinę temu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:34:54
Możesz użyć kursora w ten sposób:
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
Pisałem o tym na moim blogu tutaj: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-07 13:24:19
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
*/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-27 11:41:10
Przetestowałem z powodzeniem prosty kod poniżej
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-05-20 11:27:24