ALTER DATABASE nie powiodło się, ponieważ nie można umieścić blokady w bazie danych


124

Muszę ponownie uruchomić bazę danych, ponieważ niektóre procesy nie działają. Mój plan polega na przełączeniu go do trybu offline i ponownym włączeniu do trybu online.

Próbuję to zrobić w Sql Server Management Studio 2008:

use master;
go
alter database qcvalues
set single_user
with rollback immediate;
alter database qcvalues
set multi_user;
go

Otrzymuję te błędy:

Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.

Co ja robię źle?


Jaki jest problem, który spowodował tę potrzebę w pierwszej kolejności? Czy masz w tej chwili jakieś wycofywane transakcje? Czy uruchomiłeś już to polecenie w innym oknie SSMS, które może być nadal otwarte? Zastanawiam się (czysta spekulacja), czy może to spowodować blokadę, która blokuje inne próby, ale nadal czeka, zanim baza danych będzie mogła faktycznie zostać przełączona w tryb pojedynczego użytkownika.
Martin Smith

1
@Martin - w porządku. Muszę pomyśleć o czymś innym lub stracić rozum. jedno i drugie jest całkiem możliwe
kodowaniebadger

@thank jesteś bardzo wszyscy, i wznowiona SSMS i był w stanie zabić każdego
JOE skeet

To może być inteligencja. Usunąłem niekompletne zapytanie, które zawierało faliste linie, próbując uzyskać dostęp do bazy danych, a następnie zadziałało.
Faahmed

Odpowiedzi:


293

Po pojawieniu się błędu uruchom

EXEC sp_who2

Poszukaj bazy danych na liście. Możliwe, że połączenie nie zostało zakończone. Jeśli znajdziesz jakieś połączenia z bazą danych, uruchom

KILL <SPID>

gdzie <SPID>jest SPID dla sesji, które są połączone z bazą danych.

Wypróbuj skrypt po usunięciu wszystkich połączeń z bazą danych.

Niestety nie mam powodu, dla którego widzisz problem, ale tutaj jest link, który pokazuje, że problem wystąpił gdzie indziej.

http://www.geakeit.co.uk/2010/12/11/sql-take-offline-fails-alter-database-failed-because-a-lock-could-not-error-5061/


Czy możesz wyjaśnić, dlaczego połączenie nie zostało zakończone przez polecenie? Jedynym powodem, dla którego mogę pomyśleć, jest to, że nadal jest w trakcie wycofywania lub jest to set single_userpróba, która wciąż trwa.
Martin Smith

@ Martin, obawiam się, że nie mam ku temu powodu. Ale dodam link wskazujący, że inni widzieli problem. Zgadzam się, że cofnięcie transakcji może być problemem, ale też KILLby go nie rozwiązało.
bobsleje

Miło jest zrozumieć, dlaczego tak się dzieje, ale komentarze na Twoim łączu wydają się wskazywać, że to zadziała! (+1)
Martin Smith

KILL (87) skutkuje Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.erm ....
Tim Abell

2
@MartinSmith Myślę, że wiem, dlaczego: po prostu miałem ten sam problem, utrzymujące się połączenie pojawiające się pod sp_who2 powodujące zawieszanie się w trybie offline. Okazało się, że jest to otwarte okno edycji wierszy w SSMS. Uważam, że to, co się tutaj dzieje, to fakt, że okno edycji wierszy jest zapytaniem otwartym z edytowalnym zestawem wyników. SQL Server ma taką funkcję jako alternatywę dla instrukcji aktualizacji. Po zamknięciu tego konkretnego okna SSMS zawieszanie się kończyło się natychmiastowo.
John

5

Udało mi się odtworzyć ten błąd, wykonując następujące czynności.

Połączenie 1 (pozostaw uruchomione na kilka minut)

CREATE DATABASE TESTING123
GO

USE TESTING123;

SELECT NEWID() AS X INTO FOO
FROM sys.objects s1,sys.objects s2,sys.objects s3,sys.objects s4 ,sys.objects s5 ,sys.objects s6

Połączenia 2 i 3

set lock_timeout 5;

ALTER DATABASE TESTING123 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


1

Dodam to tutaj na wypadek, gdyby ktoś miał tyle szczęścia co ja.

Przeglądając listę procesów sp_who2 zwróć uwagę na procesy, które działają nie tylko dla wykonanej bazy danych, ale także dla mastera . W moim przypadku problem polegający na blokowaniu bazy danych był związany z procedurą składowaną, która uruchomiła xp_cmdshell.

Sprawdź, czy masz jakiekolwiek procesy w stanie KILL / RollBack dla bazy danych master

SELECT *
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'

Jeśli masz ten sam problem, samo polecenie KILL prawdopodobnie nie pomoże. Możesz zrestartować serwer SQL lub lepszym sposobem jest znalezienie cmd.exe w procesach systemu Windows w systemie operacyjnym SQL Server i zabicie go.


0

W SQL Management Studio przejdź do Security -> Logins i kliknij dwukrotnie swój login. Wybierz Role serwera z lewej kolumny i sprawdź, czy jest zaznaczona opcja sysadmin.

W moim przypadku zalogowałem się na konto bez tego uprawnienia.

HTH!


1
Błąd w pierwotnym pytaniu zdarza się również, gdy jesteś SA, nie ma to nic wspólnego z twoimi prawami. Jeśli nie masz wystarczających uprawnień, nie będziesz w stanie wykonać polecenia offline.
Abel

0

Zabicie identyfikatora procesu działało dobrze dla mnie. Podczas uruchamiania polecenia „EXEC sp_who2” w nowym oknie zapytania ... i filtruj wyniki dla „zajętej” bazy danych. Zabijanie procesów poleceniem „KILL” załatwiło sprawę. Potem wszystko znowu działało.


0

Dodam tylko moje dwa centy. Postawiłem się w tej samej sytuacji, szukając minimalnych wymaganych uprawnień logowania bazy danych, aby pomyślnie uruchomić instrukcję:

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Wydaje się, że instrukcja ALTER kończy się pomyślnie , gdy jest wykonywana z logowaniem sysadmin , ale wymaga części czyszczenia połączeń, gdy jest wykonywana z loginem, który ma „tylko” ograniczone uprawnienia, takie jak:

ALTER ANY DATABASE

PS Spędziłem wiele godzin próbując dowiedzieć się, dlaczego "ALTER DATABASE .." nie działa, gdy jest uruchamiany z loginem, który ma rolę dbcreator + uprawnienia ZMIENIA DOWOLNEJ BAZY DANYCH . Oto mój wątek MSDN !


0

Wiem, że to stary post, ale ostatnio napotkałem bardzo podobny problem. Niestety nie mogłem użyć żadnego z poleceń alter database, ponieważ nie można było nałożyć blokady na wyłączność. Ale nigdy nie udało mi się znaleźć otwartego połączenia z bazą danych. Ostatecznie musiałem wymusić usunięcie stanu kondycji bazy danych, aby zmusić ją do stanu przywracania zamiast odzyskiwania.


0

W rzadkich przypadkach (np. Po zatwierdzeniu ciężkiej transakcji) działający proces systemowy CHECKPOINT utrzymujący blokadę FILE na pliku bazy danych uniemożliwia przejście do trybu MULTI_USER.


0

W moim scenariuszu nie było procesu blokującego bazę danych pod sp_who2. Jednak odkryliśmy, że baza danych jest znacznie większa niż inne nasze bazy danych, że oczekujące procesy nadal działają, dlatego baza danych w grupie dostępności nadal jest wyświetlana jako czerwona / offline po próbie „wznowienia danych”, klikając prawym przyciskiem myszy wstrzymaną bazę danych.

Aby sprawdzić, czy nadal masz uruchomione procesy, wykonaj to polecenie: wybierz procent ukończenia z sys.dm_exec_requests, gdzie percent_complete> 0

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.