Przed natychmiastowym oznaczeniem jako duplikat przeczytałem artykuł Mike'a Walsha: Dlaczego dziennik transakcji wciąż rośnie lub brakuje miejsca? , ale nie sądzę, że dało to odpowiedź na moją sytuację. Przejrzałem kilkanaście podobnych pytań, ale najważniejsze z nich po prostu powiedziały „duplikuj” i wskazały na pytanie Mike'a.
Szczegóły: Mam kilka ~ 500 MB baz danych na SQL Server 2008 R2, wszystkie w trybie odzyskiwania SIMPLE (nie mój wybór), nocne kopie zapasowe z ~ 200 MB plików danych i ~ 300 MB plików dziennika. Dziennik nie rośnie do 300 MB od razu, ale raczej powoli w ciągu kilku miesięcy. Żadna z nich nie zawiera otwartych transakcji, przynajmniej według sp_who2 i monitora aktywności. Jeśli kliknę bazę danych prawym przyciskiem myszy i wybiorę właściwości, wyświetli się informacja, że ~ 50 MB jest za darmo. Czy cały dziennik powinien być wolny, szczególnie tuż po utworzeniu kopii zapasowej? Czy w trybie PROSTYM dziennik nie powinien być wolny, dopóki nie ma otwartej transakcji?
log_reuse_wait_descfrom sys.databasesmówi mówi „NIC”, co na podstawie powyższego pytania i odpowiedzi mówi, że nie powinno czekać na nic, aby ponownie wykorzystać przestrzeń.
Jeśli zrobię „DBCC SHRINKFILE”, plik dziennika zmniejszy się do 1 MB, więc jest gotowy odzyskać miejsce. Mogę skonfigurować coś, co zmniejsza dzienniki co tydzień i nie pozwala mi wymknąć się spod kontroli, ale jestem zdezorientowany, dlaczego SQL Server kazałby mi to zrobić.
Rozumiem, czy była jakaś szalona transakcja, która wymagała 300 MB, aby ją zalogować, ale nie robimy nic ekstremalnego, tylko podstawowy OLTP. Z pytania / odpowiedzi Mike'a:
Prosty model odzyskiwania - więc dzięki powyższemu wstępowi najłatwiej jest mówić o prostym modelu odzyskiwania. W tym modelu mówisz SQL Serverowi - nic mi nie jest, gdy używasz pliku dziennika transakcji do przywracania po awarii i wznawiasz odzyskiwanie (naprawdę nie masz wyboru. Poszukaj właściwości ACID, a to powinno mieć sens szybko), ale kiedy już nie dłużej potrzebujesz go do tego celu odzyskiwania po awarii / zrestartowaniu, śmiało i ponownie użyj pliku dziennika.
SQL Server nasłuchuje tego żądania w prostym odzyskiwaniu i zachowuje tylko informacje potrzebne do przywrócenia awarii / ponownego uruchomienia. Po upewnieniu się, że SQL Server może odzyskać, ponieważ dane są zahartowane w pliku danych (mniej więcej), dane, które zostały zahartowane, nie są już potrzebne w dzienniku i są oznaczone do obcięcia - co oznacza, że zostaną ponownie użyte.
Ciągle mówi, że przestrzeń na dzienniki powinna zostać ponownie wykorzystana, ale przy tak powolnym wzroście w ciągu miesięcy nie wydaje się, aby tak było.
czego mi brakuje? Czy coś powstrzymuje SQL Servera od rozpoznania danych jako „zahartowanych” i zwolnienia dziennika?
(edytuj) Raport After Action - AKA trochę wiedzy jest niebezpieczna
Po odkryciu, że jest to „popularne pytanie”, poczułem, że jestem winien wyjaśnienie tego, co wydarzyło się 7 miesięcy temu i czego nauczyłem się, jak mam nadzieję, ocalić innym ludziom smutek.
Po pierwsze, przestrzeń dostępna w SSMS podczas przeglądania właściwości w bazie danych to przestrzeń dostępna w pliku danych. Możesz to wyświetlić, uruchamiając następujące polecenie w bazie danych, a przekonasz się, że dostępne miejsce zgłoszone przez SSMS stanowi różnicę między FileSizeMB a UsedSpaceMB:
SELECT
DB.name,
MF.physical_name,
MF.type_desc AS FileType,
MF.size * 8 / 1024 AS FileSizeMB,
fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,
mf.name LogicalName
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.name = 'yourdatabasename'To potwierdziło, że w normalnych okolicznościach używaliśmy bardzo mało miejsca w dzienniku (20 MB lub mniej), ale prowadzi to do drugiego elementu ...
Po drugie, moje postrzeganie kłód rośnie powoli. Jednak w rzeczywistości dzienniki szybko rosły w nocy, gdy facet odpowiedzialny za stosowanie poprawek do tej aplikacji innej firmy nakładał łatki. Łatka została wykonana jako pojedyncza transakcja, więc w zależności od łaty 200 MB danych wymagało 300 MB dziennika. Kluczem do śledzenia tego było zapytanie Aarona Bertranda z https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
DatabaseName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType = CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
EventClass IN (92,93)
ORDER BY
StartTime DESC;To pokazało, że log rośnie w niektóre wieczory, kiedy klient nie korzysta z bazy danych. Doprowadziło to do rozmowy z facetem nakładającym łatki i odpowiedzi na tajemnicę.
Jeszcze raz dziękuję za osoby, które udzieliły mi pomocy w uzyskaniu odpowiedzi.