Serwer SQL - najlepsze praktyki dotyczące powiększania plików baz danych

Od dwóch tygodni monitoruję wzrost plików za pośrednictwem modułu gromadzącego dane na serwerze SQL Server 2008 R2. Baza danych stale rośnie w tempie około 35 (MB) / dzień. Baza danych jeszcze nie osiągnęła początkowego rozmiaru 2 GB.

Automatyczny wzrost plików DB jest ustawiony na 5 MB i chciałbym spróbować innego podejścia, więc szukam sugestii i komentarzy.

Istnieje zadanie dostrajania, które jest uruchamiane co tydzień w niedzielę wieczorem o 1:30 rano. Zadanie będzie:

• Sprawdź integralność bazy danych
• Zmniejsz plik dziennika - (jest w porządku, ponieważ tryb rejestrowania jest prosty)
• Zmniejsz bazę danych
• Reorganizuj indeks
• Przebuduj indeks
• Zaktualizuj statystyki
• Historia sprzątania

Chciałbym dodać jeszcze dwa kroki do cotygodniowego planu strojenia:

1. Zwiększ plik bazy danych o 500 MB, jeśli używane miejsce osiągnie określony próg lub całkowity rozmiar.
2. Zwiększ plik dziennika o 250 MB (po zmniejszeniu), jeśli używane miejsce osiągnie określony próg całkowitego rozmiaru.

Obciążając wzrost w godzinach offline, mam nadzieję na zwiększenie wydajności poprzez zmniejszenie liczby zdarzeń automatycznego wzrostu podczas dużych obciążeń.

Mam dwa pytania dotyczące automatycznie rosnących plików.

• Najlepszym miejscem do umieszczenia pliku kroki wzrostu będą przed bieżącymi krokami lub po?
• Jeśli używam ALTER DATABASE|MODIFY FILEdo powiększenia pliku, to jak mogę ustalić, czy SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)?

Powinieneś dążyć do automatycznego rozwijania się jak najmniej. Siedem razy dziennie jest dręczące, nawet przy natychmiastowej inicjalizacji plików.

Nie rób bazy danych zmniejszania. Zawsze. Może Shrinkfile, ale dopiero po nadzwyczajnym wydarzeniu. Zmniejszenie go tylko po to, by odrosnąć, jest ćwiczeniem daremności i powinno być nazwane auto-fragmentacją.

Jeśli model odzyskiwania jest prosty, na ziemi nie ma mowy, aby zwiększyć rozmiar pliku dziennika o 250 GB. Zużyte miejsce w pliku zostanie z czasem automatycznie wyczyszczone, chyba że miesiąc temu rozpocząłeś transakcję i nie masz zamiaru nigdy jej zatwierdzać ani wycofywać.

Tak więc moja rada brzmiałaby:

Automatycznie powiększaj plik danych ręcznie w cichym okresie do rozmiaru, który pomieści kilka miesięcy wzrostu. Na co tymczasem oszczędzasz?

Ustaw przyrost automatycznego wzrostu dla pliku danych na coś relatywnie małego (aby nie przeszkadzał użytkownikom, kiedy to się stanie) i ostrzegaj o tym zdarzeniu (możesz złapać go w domyślnym śladzie, na przykład lub poprzez rozszerzone wydarzenia). To może ci powiedzieć, że osiągasz najwyższy punkt, który oszacowałeś, i nadszedł czas, aby odrosnąć ręcznie. W tym momencie będziesz chciał zachować tę instrukcję na wypadek, gdybyś chciał dodać nowy plik / grupa plików na innym dysku, aby pomieścić miejsce, ponieważ ostatecznie zapełnisz bieżący dysk.

Automatycznie powiększ plik dziennika, powiedzmy, dwa razy większy niż kiedykolwiek. Nie powinno się automatycznie rozwijać, chyba że wystąpią jakieś nienormalne transakcje. Powinieneś również monitorować to wydarzenie, abyś wiedział o nich.

Auto wzrost jest czymś, czego powinieneś unikać, jeśli to możliwe. Problem polega na tym, że nie masz kontroli nad tym, kiedy wzrost może nastąpić, a twój system może poważnie uderzyć.

Ustaw rozmiar pliku na coś rozsądnego przez około miesiąc i monitoruj tempo wzrostu, a następnie sprawdź, ile miejsca szacujesz na X czasu i ustaw swój rozmiar na ten plus margines błędu.

Skonfigurowałem proste zadanie monitorowania, które ostrzeże mnie, gdy rozmiar pliku osiągnie zdefiniowane maksimum przed automatycznym wzrostem. Możesz użyć czegoś takiego:

SELECT instance_name,
       [Data File(s) Size (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used]
       into ##Logsize
FROM
(
   SELECT *
   FROM sys.dm_os_performance_counters
   WHERE counter_name IN
   (
       'Data File(s) Size (KB)',
       'Log File(s) Size (KB)',
       'Log File(s) Used Size (KB)',
       'Percent Log Used'
   )
     AND instance_name = 'database your interested in' 
) AS Src
PIVOT
(
   MAX(cntr_value)
   FOR counter_name IN
   (
       [Data File(s) Size (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used]
   )
) AS pvt 
go
declare @logsize int
Select @logsize = [Percent Log Used] from ##Logsize

If @logsize > the maximum percent you want the log to fill too i.e 90
    BEGIN
        --Do your thing here
    END

Drop table ##Logsize

Można to oczywiście zaplanować jako zadanie.