Jak zmniejszyć rozmiar pliku dziennika programu SQL Server

Nie mogę wymyślić, jak zmniejszyć rozmiar pliku ldf bazy danych.

DBA mówi, że powinienem użyć backup log dbname with truncate_only

I chociaż wygląda na to, że został poprawnie wykonany w narzędziu SQL Query Analyzer, plik ldf wciąż ma ponad 2 Gb.

** Wyjaśnienie na podstawie niektórych komentarzy i niektórych odpowiedzi poniżej. *** Konkretna baza danych, o której mowa, to baza danych na moim laptopie i używam jej wyłącznie do procesów programistycznych. Plik dziennika rozwijał się do punktu, w którym wyglądało na spowodowanie pełnego dysku. Nie wiąże się to z ryzykiem produkcyjnym. Rozumiem, że metoda w pytaniu, które zadałem, i odpowiedź, którą zaakceptowałem, są ryzykowne w środowisku produkcyjnym. *

sql-server log-files shrink

— Ron Tuffin
źródło

na pewno jest to duplikat pytania?

— JamesRyan

Spojrzałem i mogłem znaleźć tylko jedno, które zadaje pytanie, kiedy plik SHRINKFILE zawiedzie. Wówczas nie miało to sensu, więc zadałem to pytanie. Zastanawiałem się nad usunięciem pytania, ale potem pomyślałem, że będą inni, którzy będą na tej samej łodzi. Jeśli znajdziesz duplikat pytania (które faktycznie zadaje to samo pytanie, a nie podobne), chętnie go usunę.

— Ron Tuffin

Na pierwszej stronie wyszukiwania znajduje się około 8 odpowiedzi, które ją zawierają, ale myślę, że musisz wiedzieć, czego szukasz. Widzę to tak regularnie jako część odpowiedzi, byłem zaskoczony, że nie zadawano jej tak prosto.

— JamesRyan

Odpowiedź zależy w dużej mierze od opcji odzyskiwania bazy danych: prosta czy pełna?

— Richard

Dzięki za wyjaśnienie, Ron. Ponieważ jest to baza danych deweloperów, oprócz zmniejszenia pliku dziennika należy zmienić model odzyskiwania na SIMPLE, w przeciwnym razie problem wystąpi ponownie.

— BradC,

Odpowiedzi:

Och, horror! Proszę przestań mówić ludziom, że powinni zmniejszać swoje pliki dziennika!

Jeśli znalazłeś się w takiej sytuacji, bardzo prawdopodobne jest jedno z następujących przypadków:

Twoja baza danych jest w trybie pełnego odzyskiwania i naprawdę powinna być w trybie prostym
Twoja baza danych jest w trybie pełnego odzyskiwania i powinieneś regularnie wykonywać kopie zapasowe dziennika
Baza danych znajduje się w trybie pełnego odzyskiwania, a kopie zapasowe dziennika z jakiegoś powodu kończą się niepowodzeniem
Prowadzisz ogromne transakcje, które powiększają plik dziennika do ogromnych rozmiarów

Odpowiedź na każdy z nich jest następująca:

Jeśli (1), to przełącz bazę danych w tryb prosty
If (2), a następnie zaplanuj regularne kopie zapasowe dziennika
If (3), a następnie napraw zaplanowane kopie zapasowe dziennika
If (4), to po prostu nie rób tego :) Zamiast tego pracować w mniejszych partiach.

Zauważ, że ŻADNY z nich nie wymaga użycia (przestarzałego) „dbname dziennika kopii zapasowej z opcją truncate_only”

Zamiast tego, po wyczyszczeniu pliku dziennika przy użyciu jednej z powyższych technik, zmniejsz dziennik (teraz pusty) za pomocą:

DBCC SHRINKFILE ('log logical name', 2000)

Zawsze określaj rozsądny rozmiar końcowy, w przeciwnym razie zmniejszy się do prawie 0, a następnym razem będzie potrzebował czasu na rozwój.

— BradC
źródło

szkoda, że odpowiedź była tak szybka. To jedno z pytań, z których korzystam, aby odrzucić administratorów SQL podczas wywiadów. Jeśli wrócą z kopią zapasową z truncate_only, która liczy się jako 2 uderzenia nietoperza.

— Jim B

Zgadzam się, że to absolutnie ostatnia rzecz do zrobienia, zmniejsz plik. Właściwa konserwacja eliminuje taką potrzebę. Ale kiedy będzie duży i chcesz go zmniejszyć, musisz go zmniejszyć. Jednak podczas zmniejszania lepiej jest zmniejszyć plik tak mały, jak to możliwe, a następnie powiększać go do właściwego rozmiaru w krokach co 8 GB. To zoptymalizuje liczbę VLF w pliku. Zobacz - sqlskills.com/BLOGS/KIMBERLY/post/… .

— Brian Knight

Interesujący link, wygląda na to, że ma on zastosowanie tylko wtedy, gdy Twój dziennik trans ma ponad 8 Gb. Myślę, że punktem BradC (a przynajmniej moim) jest to, że tak, są sytuacje awaryjne, które spowodują, że zmniejszysz plik dziennika, ale powinieneś rozpoznać, że jeśli uruchamiasz notoryczny backup / w trunc, a następnie plik zmniejszania, właśnie ukryłeś swój łańcuch kopii zapasowych (mam nadzieję, że to nie było nic ważnego), a oprócz problemów z miejscem na dysku prawdopodobnie masz poważne problemy z serwerem SQL, prawdopodobnie z perspektywy projektowania bazy danych lub architektury. Bez naprawiania leżącej u podstaw sprawy, w najlepszym razie kupiłeś sobie trochę czasu.

— Jim B

po wykonaniu „tworzenia kopii zapasowej za pomocą truncate_only” należy wydać następujące polecenie, aby zmniejszyć

dbcc SHRINKFILE (logfilename,shrink_tosize)

na przykład

dbcc SHRINKFILE (mydatabase_Log,512)

— Mani
źródło

Skrypt napisany powyżej oznaczy zawartość dziennika do ponownego użycia. Postępuj zgodnie ze skryptem za pomocą:

USE <database>;

DBCC SHRINKFILE (<log logical file name>)

To zmniejszy to dla ciebie.

— Brian Knight
źródło