Jak zabić wszystkie bieżące połączenia z bazą danych SQL Server 2005?

Chcę zmienić nazwę bazy danych, ale wciąż pojawia się błąd, że „nie można uzyskać wyłącznej blokady” w bazie danych, co oznacza, że ​​niektóre połączenia są nadal aktywne.

Jak mogę zabić wszystkie połączenia z bazą danych, aby móc zmienić jej nazwę?

Powodem, dla którego Adam zasugerował, że nie zadziała, jest to, że w czasie, gdy zapętlasz aktywne połączenia, można ustanowić nowe, a ty ich przegapisz. Zamiast tego możesz zastosować następujące podejście, które nie ma tej wady:

-- set your current connection to use master otherwise you might get an error

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER

Skrypt, aby to zrobić, zamień „DB_NAME” na bazę danych, aby zabić wszystkie połączenia z:

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

Zabij go i zabij ogniem:

USE master
go

DECLARE @dbname sysname
SET @dbname = 'yourdbname'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Za pomocą SQL Management Studio Express:

W drzewie Eksploratora obiektów przejdź do sekcji Zarządzanie do „Monitor aktywności” (jeśli nie możesz go tam znaleźć, kliknij prawym przyciskiem myszy serwer bazy danych i wybierz „Monitor aktywności”). Otwierając Monitor aktywności, możesz wyświetlić wszystkie informacje o procesie. Powinieneś być w stanie znaleźć blokady dla bazy danych, którą jesteś zainteresowany, i zabić te blokady, które również zabiją połączenie.

Po tym powinieneś być w stanie zmienić nazwę.

Zawsze używałem:

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
SP_RENAMEDB 'DB_NAME','DB_NAME_NEW'
Go 
ALTER DATABASE DB_NAME_NEW  SET MULTI_USER -- set back to multi user 
GO 

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

Odłączenie się zajmuje trochę czasu, a czasem mam z tym pewne problemy ...

Najbardziej solidny sposób moim zdaniem:

Odłącz Kliknij prawym przyciskiem myszy DB -> Zadania -> Odłącz ... zaznacz „Zrzuć połączenia” Ok

Ponownie podłącz Kliknij prawym przyciskiem myszy Bazy danych -> Załącz .. Dodaj ... -> wybierz bazę danych i zmień kolumnę Dołącz jako na żądaną nazwę bazy danych. Dobrze

Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'your db name'

Declare @query nvarchar(max)
--Select * from #temp
Select @query =STUFF((                              
            select '  ' + KillCommand from #temp
            FOR XML PATH('')),1,1,'') 
Execute sp_executesql @query 
Drop table #temp

użyj „głównej” bazy danych i uruchom tę kwerendę, zabije ona wszystkie aktywne połączenia z bazy danych.

Zwykle napotykam ten błąd, gdy próbuję przywrócić bazę danych. Zwykle po prostu wchodzę na szczyt drzewa w Management Studio i klikam prawym przyciskiem myszy i ponownie uruchamiam serwer bazy danych (ponieważ znajduje się na komputerze programistycznym, może nie być idealny w środowisku produkcyjnym ). To zamyka wszystkie połączenia z bazą danych.

W MS SQL Server Management Studio w eksploratorze obiektów kliknij bazę danych prawym przyciskiem myszy. W menu kontekstowym, które następuje, wybierz „Zadania -> Przełącz offline”

Innym podejściem „zabić go ogniem” jest ponowne uruchomienie usługi MSSQLSERVER. Lubię robić rzeczy z wiersza poleceń. Wklei to dokładnie do CMD to zrobi: NET STOP MSSQLSERVER i NET START MSSQLSERVER

Lub otwórz „services.msc” i znajdź „SQL Server (MSSQLSERVER)” i kliknij prawym przyciskiem myszy, wybierz „uruchom ponownie”.

To „na pewno na pewno” zabije WSZYSTKIE połączenia z WSZYSTKIMI bazami danych uruchomionymi w tej instancji.

(Podoba mi się to bardziej niż wiele podejść, które zmieniają i przywracają konfigurację na serwerze / bazie danych)

Oto jak niezawodnie tego rodzaju rzeczy w MS SQL Server Management Studio 2008 (może działać również w przypadku innych wersji):

1. W drzewie eksploratora obiektów kliknij prawym przyciskiem myszy główny serwer bazy danych (zieloną strzałką), a następnie kliknij monitor aktywności.
2. Otwórz kartę procesów w monitorze aktywności, wybierz menu rozwijane „bazy danych” i filtruj według żądanej bazy danych.
3. Kliknij prawym przyciskiem myszy bazę danych w Eksploratorze obiektów i uruchom zadanie „Zadania -> Przełącz offline”. Pozostaw to uruchomione w tle, podczas gdy ...
4. Bezpiecznie wyłącz wszystko, co możesz.
5. Zabij wszystkie pozostałe procesy z zakładki procesu.
6. Ponownie włącz DB.
7. Zmień nazwę bazy danych.
8. Ponownie włącz usługę i skieruj ją do nowej bazy danych.

Opcja działająca dla mnie w tym scenariuszu jest następująca:

1. Rozpocznij operację „Odłącz” w danej bazie danych. Spowoduje to otwarcie okna (w SQL 2005) wyświetlającego aktywne połączenia, które zapobiegają działaniom na DB.
2. Zabij aktywne połączenia, anuluj operację odłączania.
3. Baza danych powinna być teraz dostępna do przywracania.

Spróbuj tego:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Kliknij prawym przyciskiem myszy nazwę bazy danych, kliknij Właściwość, aby wyświetlić okno właściwości, Otwórz kartę Opcje i zmień właściwość „Ogranicz dostęp” z Wiele użytkowników na Pojedyncze. Po naciśnięciu przycisku OK pojawi się monit o zamknięcie wszystkich otwartych połączeń, wybierz „Tak” i możesz zmienić nazwę bazy danych ....

Nie działały one dla mnie (SQL2008 Enterprise), nie widziałem też żadnych uruchomionych procesów ani użytkowników podłączonych do bazy danych. Ponowne uruchomienie serwera (kliknij prawym przyciskiem myszy Sql Server w Management Studio i wybierz Uruchom ponownie) pozwoliło mi przywrócić DB.

Korzystam z programu SQL Server 2008 R2, moja baza danych została już ustawiona dla jednego użytkownika i istniało połączenie, które ograniczało wszelkie działania w bazie danych. Zatem zalecane rozwiązanie SQLMenace zareagowało błędem. Oto taki, który zadziałał w moim przypadku .

Używam sp_who, aby uzyskać listę wszystkich procesów w bazie danych. Jest to lepsze, ponieważ możesz chcieć sprawdzić, który proces zabić.

declare @proc table(
    SPID bigint,
    Status nvarchar(255),
    Login nvarchar(255),
    HostName nvarchar(255),
    BlkBy nvarchar(255),
    DBName nvarchar(255),
    Command nvarchar(MAX),
    CPUTime bigint,
    DiskIO bigint,
    LastBatch nvarchar(255),
    ProgramName nvarchar(255),
    SPID2 bigint,
    REQUESTID bigint
)

insert into @proc
exec sp_who2

select  *, KillCommand = concat('kill ', SPID, ';')
from    @proc

Wynik
Możesz użyć polecenia w kolumnie KillCommand, aby zabić żądany proces.

SPID    KillCommand
26      kill 26;
27      kill 27;
28      kill 28;

Możesz użyć polecenia SP_Who i zabić cały proces korzystający z bazy danych, a następnie zmienić nazwę bazy danych.