Jak zmniejszyć rozmiar kopii zapasowej dziennika transakcji po pełnej kopii zapasowej?

Mam trzy plany konserwacji skonfigurowane do działania w instancji Sql Server 2005:

• Cotygodniowe optymalizacje bazy danych, a następnie pełna kopia zapasowa
• Codzienna różnicowa kopia zapasowa
• Kopie zapasowe dzienników transakcji

Cogodzinne kopie zapasowe dziennika wynoszą zwykle od kilkuset Kb do 10 Mb, w zależności od poziomu aktywności, dzienne różnice zwykle rosną do około 250 Mb do końca tygodnia, a tygodniowa kopia zapasowa wynosi około 3,5 GB.

Problem, który mam, polega na tym, że optymalizacje przed pełną kopią zapasową wydają się powodować, że następna kopia zapasowa dziennika transakcji powiększy się ponad dwukrotnie do wielkości pełnej kopii zapasowej, w tym przypadku 8 GB, przed powrotem do normalnego stanu.

Poza tym BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY, czy jest jakiś sposób, aby zmniejszyć rozmiar kopii zapasowej dziennika lub w ogóle zapobiec zapisywaniu optymalizacji w dzienniku transakcji, ponieważ na pewno zostaną one uwzględnione w pełnej kopii zapasowej, którą poprzedzają?

Kilka interesujących sugestii, które wydają się nieporozumieniem na temat działania kopii zapasowych dzienników. Kopia zapasowa dziennika zawiera WSZYSTKIE dzienniki transakcji wygenerowane od czasu utworzenia poprzedniej kopii zapasowej dziennika, niezależnie od tego, jakie kopie zapasowe są pełne lub różnicowe. Zatrzymywanie tworzenia kopii zapasowych dzienników lub przechodzenie do codziennych pełnych kopii zapasowych nie będzie miało wpływu na rozmiary kopii dzienników. Jedyną rzeczą, która wpływa na dziennik transakcji, jest kopia zapasowa dziennika po uruchomieniu łańcucha kopii zapasowej dziennika.

Jedynym wyjątkiem od tej reguły jest zerwanie łańcucha kopii zapasowej dziennika (np. Przejście do modelu odzyskiwania SIMPLE, przywrócenie z migawki bazy danych, obcięcie dziennika przy użyciu BACKUP LOG WITH NO_LOG / TRUNCATE_ONLY), w którym to przypadku pierwsza kopia zapasowa dziennika będzie zawierał cały dziennik transakcji od ostatniej pełnej kopii zapasowej - co ponownie uruchamia łańcuch kopii zapasowej dziennika; lub jeśli łańcuch tworzenia kopii zapasowej dziennika nie został uruchomiony - po przejściu na FULL po raz pierwszy, działasz w rodzaju pseudo-PROSTEGO modelu odzyskiwania do momentu wykonania pierwszej pełnej kopii zapasowej.

Aby odpowiedzieć na pierwotne pytanie, bez wchodzenia w model odzyskiwania SIMPLE, będziesz musiał zassać kopię zapasową całego dziennika transakcji. W zależności od podejmowanych działań możesz wykonywać częstsze kopie zapasowe dziennika, aby zmniejszyć ich rozmiar, lub wykonywać bardziej ukierunkowaną bazę danych.

Jeśli możesz opublikować informacje o wykonywanych operacjach konserwacyjnych, mogę pomóc w ich optymalizacji. Czy przypadkiem przeprowadzasz przebudowę indeksu, a następnie zmniejszasz bazę danych, aby odzyskać miejsce używane przez przebudowę indeksu?

Jeśli nie ma żadnej innej aktywności w bazie danych podczas konserwacji, możesz wykonać następujące czynności:

• upewnij się, że aktywność użytkownika została zatrzymana
• zrób ostateczną kopię zapasową dziennika (pozwala to odzyskać aż do momentu rozpoczęcia konserwacji)
  • przejdź do modelu odzyskiwania SIMPLE
  • wykonaj konserwację - dziennik zostanie obcięty w każdym punkcie kontrolnym
  • przejdź do pełnego modelu odzyskiwania i wykonaj pełną kopię zapasową
  • kontynuować jak zwykle

Mam nadzieję, że to pomoże - czekamy na więcej informacji.

Dzięki

[Edycja: po całej dyskusji na temat tego, czy pełna kopia zapasowa może zmienić rozmiar kolejnej kopii zapasowej dziennika (nie może), przygotowałem obszerny post na blogu z materiałem w tle i skryptem, który to potwierdza. Sprawdź to na https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]

Możesz je zmniejszyć, ale po prostu odrosną, ostatecznie powodując fragmentację dysku. Przebudowy indeksu i defragmentacje tworzą bardzo duże dzienniki transakcji. Jeśli nie potrzebujesz możliwości odzyskiwania w określonym momencie, możesz przejść do trybu prostego odzyskiwania i całkowicie zrezygnować z tworzenia kopii zapasowych dziennika transakcji.

Domyślam się, że używasz planu konserwacji do optymalizacji, możesz go zmienić, aby użyć skryptu, który indeksuje defragmentację tylko po osiągnięciu określonego poziomu fragmentacji i prawdopodobnie nie ucierpiałby żaden spadek wydajności. Generowałoby to znacznie mniejsze dzienniki.

Pominąłem codzienne różnice na korzyść codziennych pełnych kopii zapasowych BTW.

Twoje ostatnie pytanie brzmiało: „Poza dziennikiem tworzenia kopii zapasowej z TRUNCATE_ONLY, istnieje jakiś sposób, aby zmniejszyć rozmiar kopii zapasowej dziennika lub w ogóle zapobiec zapisywaniu optymalizacji w dzienniku transakcji, ponieważ na pewno zostaną one w pełni rozliczone kopie zapasowe poprzedzają? ”

Nie, ale oto obejście. Jeśli wiesz, że jedynymi działaniami w tej bazie danych w tym czasie będą zadania konserwacji indeksu, możesz zatrzymać tworzenie kopii zapasowych dziennika transakcji przed rozpoczęciem konserwacji indeksu. Na przykład, niektóre z moich serwerów w sobotnie wieczory, harmonogramy pracy wyglądają tak:

• 21:30 - trwa tworzenie kopii zapasowej dziennika transakcji.
• 21:45 - kopia zapasowa dziennika transakcji jest uruchamiana po raz ostatni. Harmonogram zatrzymuje się o 9:59.
• 22.00 - rozpoczyna się utrzymanie indeksu i ma wbudowane przystanki, które kończą się przed 11:30.
• 23:30 - pełne tworzenie kopii zapasowej rozpoczyna się i kończy w mniej niż 30 minut.
• 12:00 - kopie zapasowe dziennika transakcji rozpoczynają się ponownie co 15 minut.

Oznacza to, że nie mam możliwości odzyskania punktu w czasie między 21:45 a 23:30, ale korzyścią jest szybsza wydajność.

Prosta odpowiedź: zmień cotygodniowe zadanie optymalizacji, aby działało w bardziej zrównoważony sposób w nocy. tj. ponownie indeksuj tabele np. w niedzielę wieczorem, f - l w poniedziałek wieczorem itp. ... znajdź dobry balans, twój dziennik będzie średnio mniej więcej 1/6 wielkości. Oczywiście działa to najlepiej, jeśli nie korzystasz z wbudowanego zadania konserwacji indeksu ssis.

Minusem tego i znaczącym w zależności od obciążenia twoich baz danych jest to, że sieją spustoszenie w optymalizatorze i ponownym użyciu planów zapytań.

Ale jeśli zależy Ci tylko na rozmiarze c-loga co tydzień, podziel go z dnia na dzień lub z godziny na godzinę i między kopiami zapasowymi t-log.

Możesz także skorzystać z narzędzia zewnętrznego (Litespeed z Quest, SQL Backup z Red Gate, Hyperbac) w celu zmniejszenia rozmiarów kopii zapasowych i dzienników. Mogą szybko zwrócić się za oszczędności na taśmach.

Prawdopodobnie można założyć, że „optymalizacje” obejmują przebudowy indeksu. Tylko wykonywanie tych zadań co tydzień może być dopuszczalne w bazie danych, która nie napotyka dużej liczby aktualizacji i wstawek, jednak jeśli dane są bardzo płynne, możesz zrobić kilka rzeczy:

1. Odbuduj lub zreorganizuj swoje indeksy co noc, jeśli pozwala na to harmonogram i jeśli wpływ jest akceptowalny. Podczas wykonywania tych nocnych zadań związanych z konserwacją indeksu kieruj reklamy tylko na te indeksy, które są podzielone ponad powiedzmy 30% w przypadku przebudowy oraz w zakresie 15-30% w przypadku reorgów.

2. Te zadania są rejestrowanymi transakcjami, więc jeśli martwisz się wzrostem dzienników, zalecałbym to, co Paul zalecił. Kopia zapasowa dziennika transakcji końcowych przed konserwacją indeksu, przejdź do Prostego odzyskiwania, następnie wykonaj proces konserwacji, a następnie przełącz się z powrotem na Pełne odzyskiwanie, a następnie Kopia zapasowa danych powinna załatwić sprawę.

Do moich plików dziennika podchodzę jak w zen: mają taki rozmiar, jaki chcą. Tak długo, jak nie przetrwały abberalnego wzrostu z powodu złych praktyk tworzenia kopii zapasowych w porównaniu do aktywności w bazie danych, którą jest mantra, którą żyję.

Jeśli chodzi o skrypty, które wykonują dyskrecjonalną konserwację indeksu, szukaj w Internecie: jest mnóstwo. Andrew Kelly opublikował przyzwoity artykuł w SQL Magazine około rok temu. SQLServerPedia ma kilka skryptów autorstwa Michelle Ufford, a najnowsze wydanie SQL Magazine (uważam, że lipiec 2009 r.) Zawiera również pełny artykuł na ten temat. Chodzi o to, aby znaleźć taki, który działa dobrze dla Ciebie i dostosować go do własnych potrzeb przy minimalnych dostosowaniach.

Czy potrafisz specjalnie wykonać kopię zapasową dziennika transakcji w różnych punktach podczas optymalizacji bazy danych? Całkowity rozmiar dzienników t byłby taki sam, ale każdy byłby mniejszy, być może w jakiś sposób pomagając.

Czy możesz przeprowadzić bardziej ukierunkowaną optymalizację bazy danych, aby utworzyć mniej transakcji (ktoś o tym wspomniał, ale nie jestem pewien, czy implikacje zostały określone). Takie jak tolerowanie pewnej fragmentacji lub zmarnowanej przestrzeni przez jakiś czas. Jeśli 40% twoich tabel jest podzielonych tylko w 5%, nie dotknięcie ich może zaoszczędzić sporo aktywności.