Jak podczas przywracania kopii zapasowej rozłączyć wszystkie aktywne połączenia?


166

Mój SQL Server 2005 nie przywraca kopii zapasowej z powodu aktywnych połączeń. Jak mogę to wymusić?


Czy zawsze chcesz zabić wszystkie połączenia z bazą danych, którą chcesz „przywrócić”? A może będą chwile, kiedy nie będziesz chciał zabijać istniejących połączeń? Czy musisz się też martwić o buforowanie połączeń?
Philip Kelley,

Odpowiedzi:


177

SQL Server Management Studio 2005

Kliknięcie bazy danych prawym przyciskiem myszy i kliknięcie, Tasksa następnie kliknięcie Detach Databasepowoduje wyświetlenie okna dialogowego z aktywnymi połączeniami.

Odłącz ekran

Klikając na hiperłącze pod "Wiadomościami" możesz zabić aktywne połączenia.

Następnie możesz zabić te połączenia bez odłączania bazy danych.

Więcej informacji tutaj .

SQL Server Management Studio 2008

Interfejs zmienił się dla SQL Server Management studio 2008, oto kroki (przez: Tim Leung )

  1. Kliknij prawym przyciskiem myszy serwer w Eksploratorze obiektów i wybierz „Monitor aktywności”.
  2. Po otwarciu rozwiń grupę Procesy.
  3. Teraz użyj listy rozwijanej, aby przefiltrować wyniki według nazwy bazy danych.
  4. Zabij połączenia z serwerem, wybierając opcję „Zabij proces” prawym przyciskiem myszy.

21
Jeśli masz ten sam problem co @Ryan, to prawdopodobnie dlatego, że używasz Management Studio 2008 (lub nowszego), a nie Management Studio 2005. Aby zrobić to samo w Management Studio 2008, kliknij prawym przyciskiem myszy serwer w Object Eksplorator i wybierz „Monitor aktywności”. Po otwarciu rozwiń grupę Procesy. Teraz użyj listy rozwijanej, aby przefiltrować wyniki według nazwy bazy danych. Możesz teraz zabić swoje połączenia, wybierając opcję „Zabij proces” prawym przyciskiem myszy.
Tim Leung,

195

Chcesz ustawić swoją bazę danych w trybie pojedynczego użytkownika, wykonaj przywracanie, a następnie ustaw ją z powrotem na wielu użytkowników:

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

--Do Actual Restore
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\Data\YourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\Data\YourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

Źródła: Pinal Dave ( http://blog.SQLAuthority.com )

Oficjalne odniesienie: https://msdn.microsoft.com/en-us/library/ms345598.aspx


11
Zamiast wydawać NATYCHMIASTOWĄ ROLLBACK, może to mieć znaczenie tylko dla ROLLBACK po określonym OPÓŹNIENIU, dając w ten sposób zapytaniom użytkownika możliwość naturalnego zakończenia.
John Sansom,

2
Dobra uwaga, zaktualizowano do wycofywania zmian, aby zawierało polecenie AFTER 60, aby umożliwić ukończenie bieżących zapytań
brendan,

Cześć @brendan, co jeśli wycofanie zajmie więcej niż 60 sekund? dzięki
user3583912

11
Jeśli przywracasz bazę danych, otwarte transakcje zostaną utracone, niezależnie od tego, czy Ty, ROLLBACK IMMEDIATEczy ROLLBACK AFTER 60. Jedynym sposobem na zapisanie tych danych jest wykonanie kolejnej kopii zapasowej po wycofaniu. Ale przywracasz z innej kopii zapasowej. Więc jaki jest sens czekania? Czy coś mi brakuje?
Dave Mason

@DMason, też jestem ciekawy tego pytania. Czy używanie single_user z trybem wycofywania zapobiega nowym połączeniom w czasie oczekiwania? Jeśli tak, zastanawiam się, czy jest to czystszy / przyjemniejszy sposób przynajmniej na zakończenie działań tylko do odczytu, a następnie ich gwałtowne zakończenie?
Jason,

43

Ten kod działał dla mnie, zabija wszystkie istniejące połączenia z bazą danych. Wszystko, co musisz zrobić, to zmienić wiersz Set @dbname = 'databaseName', aby zawierał nazwę Twojej bazy danych.

Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

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

po tym udało mi się go przywrócić


1
To było najszybsze podejście (SingleUserMode * 20 = 60s, Kill * 20 = 5s).
Karson

Na mnie to nie wyszło. Baza danych jest nadal w użyciu. Używam SQL Server 2008.
Marek Bar

Zauważyłem, że uruchomienie tego kodu kilka razy, jeden po drugim, WCZEŚNIEJ załatwi sprawę. Czasami coś zakrada się pomiędzy twoim ZABIJANIEM a przywróceniem. A czasami musisz uruchomić zabijanie, NASTĘPNIE przywracanie jednego po drugim.
John Wacławski

Zależy całkowicie od agresywności aplikacji próbującej ponownie się połączyć. Kilku leniwych użytkowników? Działa świetnie. Serwer aplikacji o dużej objętości, który łączy się ponownie w niecałą sekundę? Nie tak bardzo.
BradC,

5

Spróbuj tego:

DECLARE UserCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
    spid
FROM
    master.dbo.sysprocesses
WHERE DB_NAME(dbid) = 'dbname'--replace the dbname with your database
DECLARE @spid SMALLINT
DECLARE @SQLCommand VARCHAR(300)
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO
    @spid
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLCommand = 'KILL ' + CAST(@spid AS VARCHAR)
    EXECUTE(@SQLCommand)
    FETCH NEXT FROM UserCursor INTO
        @spid
END
CLOSE UserCursor
DEALLOCATE UserCursor
GO

4

Ponowne uruchomienie serwera SQL rozłączy użytkowników. Najłatwiejszy sposób, jaki znalazłem - dobry również, jeśli chcesz przełączyć serwer w tryb offline.

Ale z jakiegoś bardzo dziwnego powodu opcja „Przełącz w tryb offline” nie robi tego niezawodnie i może zawiesić lub zmylić konsolę zarządzania. Ponowne uruchomienie i przejście do trybu offline działa

Czasami jest to opcja - jeśli na przykład zatrzymałeś serwer WWW, który jest źródłem połączeń.


+1. Zaakceptowana odpowiedź nie będzie działać dla SQL Express (np. W środowisku deweloperskim), ponieważ SQL Express nie ma Monitora aktywności
Matt Frear

1
@MattFrear: To nieprawda! Przynajmniej w 2008 R2 Express widzę przycisk paska narzędzi i pozycję menu kontekstowego na węźle serwera.
Stephan

4
Ponowne uruchomienie całego serwera SQL spowoduje przerwanie połączeń do wszystkich baz danych. Serwer może obsługiwać wiele baz danych, ale tylko jedna wymaga teraz przywrócenia.
Ross Presser,

3
To zdecydowanie najgorszy sposób na zabicie połączeń do 1 bazy danych. Zwłaszcza jeśli masz wiele innych baz danych, które są nadal używane przez innych użytkowników. Radzę PRZECIWKO tej metodzie. To w 100%, całkowita przesada !!
John Waclawski

@JohnWaclawski Nie wiem co najgorsze, ale na pewno najbardziej leniwe - dlatego czasami mówiłem. I tak naprawdę nie oszczędza czasu w porównaniu z innymi metodami
Simon_Weaver

3

Napotkałem ten problem podczas automatyzacji procesu przywracania w SQL Server 2008. Moje (udane) podejście było połączeniem dwóch udzielonych odpowiedzi.

Najpierw sprawdzam wszystkie połączenia wspomnianej bazy danych i zabijam je.

DECLARE @SPID int = (SELECT TOP 1 SPID FROM sys.sysprocess WHERE dbid = db_id('dbName'))
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = top 1 spid from master.dbo.sysprocesses
        where dbid = db_id('dbName')
End

Następnie ustawiłem bazę danych w trybie pojedynczego użytkownika

ALTER DATABASE dbName SET SINGLE_USER

Następnie uruchamiam przywracanie ...

RESTORE DATABASE and whatnot

Ponownie zakończ połączenia

(same query as above)

I ustaw bazę danych z powrotem na multi_user.

ALTER DATABASE dbName SET MULTI_USER

W ten sposób upewniam się, że nie ma żadnych połączeń zatrzymujących bazę danych przed przełączeniem w tryb pojedynczy, ponieważ ten pierwszy zostanie zawieszony, jeśli istnieją.


2

Żaden z nich nie działał dla mnie, nie mógł usunąć ani odłączyć obecnych użytkowników. Nie można też było zobaczyć żadnych aktywnych połączeń z bazą danych. Ponowne uruchomienie programu SQL Server (kliknij prawym przyciskiem myszy i wybierz opcję Uruchom ponownie) pozwoliło mi to zrobić.


2

Aby dodać do już podanych porad, jeśli masz aplikację internetową działającą za pośrednictwem usług IIS, która korzysta z bazy danych, może być konieczne zatrzymanie (nie ponowne użycie ) puli aplikacji dla aplikacji podczas przywracania, a następnie ponowne uruchomienie. Zatrzymanie puli aplikacji zabija aktywne połączenia HTTP i nie zezwala na więcej, co w przeciwnym razie mogłoby doprowadzić do wyzwolenia procesów, które łączą się z bazą danych, a tym samym blokują ją. Jest to znany problem dotyczący na przykład systemu zarządzania treścią Umbraco podczas przywracania bazy danych


1

Żadne z powyższych nie działało dla mnie. Moja baza danych nie pokazała żadnych aktywnych połączeń za pomocą Monitora aktywności lub sp_who. Ostatecznie musiałem:

  • Kliknij prawym przyciskiem myszy węzeł bazy danych
  • Wybierz „Odłącz ...”
  • Zaznacz pole „Porzuć połączenia”
  • Podłącz ponownie

Nie jest to najbardziej eleganckie rozwiązanie, ale działa i nie wymaga ponownego uruchamiania SQL Server (nie jest to opcja dla mnie, ponieważ serwer DB hostował kilka innych baz danych)


To jest totalna przesada. Użyj powyższego kodu KILL. Pracuje dla mnie przy setkach zleceń przywracania.
John Wacławski

Baza danych, z którą pracowałem, nie ZABIŁAby wszystkiego - jednak mógł to być problem z ich konfiguracją. Zgadzam się, że ogólnie jest to o wiele łatwiejsze.
Brent Wagoner

0

Wolę to robić,

zmiana bazy danych ustawiona w trybie offline z natychmiastowym wycofaniem

a następnie przywróć bazę danych. po tym,

zmiana bazy danych w trybie online z natychmiastowym wycofaniem

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.