Wyciekły transakcje z SQL Server

Mam bazę danych, do której dostęp ma około 50 klientów za pośrednictwem TDS przez TCP, co nie wydaje się zwalniać przestrzeni dziennika. Liczba procesów utrzymuje się na poziomie oczekiwanych 50, a niektóre z nich trwają dość długo (> 120 dni).

Baza danych ma teraz 40 GB miejsca w dzienniku (ma tylko 14 GB danych), 39 GB wolnego. Z powodu ograniczeń miejsca na dysku chciałbym się skrócić do czegoś bardziej rozsądnego (10 gb-ish). Kiedy wykonuję DBCC SHRINKFILE('db_log', 10000), zwraca błąd, że koniec dziennika jest w użyciu.

Aby uwolnić dostęp do końca dziennika, próbowałem ustawić bazę danych w trybie pojedynczego użytkownika z następującymi:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE db SET MULTI_USER
GO

ale skrypt zwraca następujący komunikat powtarzany setki razy:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Co prowadzi mnie do przekonania, że ​​gdzieś pozostawiam niektóre transakcje niezaangażowane. Nie znam żadnego procesu, który celowo otworzyłby tak wiele transakcji jednocześnie, więc myślę, że muszą się one kumulować z czasem, nigdy nie będą zamykane.

Pytanie: Jak zlokalizować naruszający proces lub skrypt lub dlaczego dziennik nie jest zwalniany?

sys.dm_tran_active_transactionspokazuje rozsądne 18 transakcji w zrozumiałych celach. sp_whopokazuje tylko procesy, których jestem świadomy.

Wersja SQL Server:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Wersja serwera:

Windows Server 2008 R2 x64 - vCPU Datacenter 4, pamięć 16 GB, dysk przechodzący do danych i dziennika, dysk systemu operacyjnego to VHD

w Hyper-V (Windows Server 2008 R2 SP1 x64 Datacenter) Dual Intel X5650 (6 rdzeni, 12 wątków przy 2,67 GHz) 72 GB pamięci

Hypervisor ma tylko trzy maszyny wirtualne i nie wykazuje dużego zużycia zasobów. SQL Server VM pokazuje ~ 40% obciążenia procesora i 99% trafień w pamięci podręcznej.

Istnieje polecenie SQL, które wyświetli transakcje OPEN. (DBCC OPENTRAN)

http://msdn.microsoft.com/en-us/library/ms182792.aspx

Wyświetla informacje o najstarszej aktywnej transakcji oraz najstarszych rozproszonych i niepodzielonych replikowanych transakcjach, jeśli takie istnieją, w określonej bazie danych. Wyniki są wyświetlane tylko wtedy, gdy istnieje aktywna transakcja lub baza danych zawiera informacje o replikacji

Z jakiegokolwiek powodu wydawało się, że nic nie trzyma pliku dziennika otwarty. Uruchamiając wiele kopii zapasowych dziennika (> 10), koniec dziennika został zwolniony i może wystąpić zmniejszenie. Nie jestem pewien, dlaczego ... ale zadziałało.

backup log db to disk = '\\l-backup1\drop\2012-12-23_db_log.bak' with stats = 1
go 15
dbcc shrinkfile('db_log', 10240)
go

Jeśli dobrze rozumiem twoje pytanie, masz dziennik transakcji 40 Gb z 39 Gb za darmo? Dziennik ma strukturę kołową, złożoną z mniejszych wirtualnych plików dziennika. Za każdym razem, gdy VLF jest pełny, SQL zaczyna używać następnego VLF. (Niekoniecznie w tej samej kolejności, w jakiej znajdują się pliki VLF). Zmniejszenie pliku dziennika zwalnia wolne miejsce z KONIEC dziennika. Jeśli aktywna część kłody znajduje się na końcu, nie można zwolnić miejsca, jeśli jest gdzieś pośrodku, można odzyskać tylko trochę miejsca. DBCC LOGINFO pokaże wszystkie VLF w logu oraz status pokazujący, że VLF obecnie zawiera dowolny aktywny log. Uważam, że status 2 jest aktywny, a 0 nieaktywny. Jestem pewien, że Google może w razie potrzeby podać więcej informacji.

Jeśli Twoim problemem jest to, że aktywna część znajduje się obecnie na końcu, najlepszym rozwiązaniem jest poczekać, aż zacznie się od początku do końca, a następnie zmniejszyć dziennik. To może zająć zaskakująco dużo czasu, bądź cierpliwy. Dotrze tam.
Pamiętaj również, że jeśli jakakolwiek część VLF jest obecnie aktywna, cały VLF pozostaje aktywny.

Powinieneś jednak monitorować rozmiar pliku dziennika, jeśli niespodziewanie wzrośnie, musisz przeprowadzić dochodzenie w sprawie przyczyny. Należy unikać niepotrzebnego zmniejszania się pliku dziennika, gdy ponownie się powiększy, może spowolnić działanie.

Więcej informacji na temat VLF można znaleźć w poście na blogu Kimberly Tripps tutaj .