Zmniejszenie pliku bazy danych jest złe, niedobre! Budzi strach i sieje spustoszenie. Tak głosi większość DBA. Czy aby na pewno tak jest? No to zaczynamy. Pierwsze pytanie, kiedy rozważać użycie polecenia SHRINKFILE?

Według mnie wtedy gdy, np. plik danych zajmuję ponad 200GB a zajęte jest 30-40GB. Wtedy, gdy na prawdę potrzebujemy przestrzeni dyskowej a użycie jest co najmniej 3-4 krotnie mniejsze niż plik mdf.

Kolejne pytania:

  • Dlaczego plik danych ma tyle wolnej przestrzeni?

    Przykładowo, wiem, że dane archiwalne po raz pierwszy została przeniesiona i usunięte. Wiem, że ilość zwolnionej przestrzeni nie zostanie szybko ponownie wypełniona danymi a zwolniona przestrzeń zostanie przeznaczona dla innych plików danych. Wtedy jak najbardziej możemy rozważyć shrinkowanie.

    Jeśli jednak nie wiemy dlaczego mamy tyle wolnego miejsca to zaniechamy wszelkie działania. Tyle przestrzeni w pliku bazy danych to na pewno następstwo jakiś operacji. Musisz zdobyć na ten temat wiedzę i dopiero wtedy ewentualnie wrócić do tematu.

  • Czy wiem jak działa i co powoduję zmniejszenie pliku mdf?

    Wykonując  SHRINKFILE (nie zawsze) lub SHRINKDATABASE, SQL Server najpierw przesuwa wypełnione strony do pierwszego dostępnego miejsca na przodzie plików, aby zwolnić miejsce na końcu. Następnie gdy koniec jest pusty, obcina go i zwalnia odpowiednią liczbę wolnych stron do systemu operacyjnego.

    Jest to proces bardzo kosztowy który pozostawia po sobie spory bałagan w postaci fragmentacji pliku danych. Należy się niezwłocznie z tym uporać wykonując zadania defragmentacji indeksów.

Tyle pytań i teorii, przejdę do praktyki. W tym celu posłużyłem się bazą StackOverflow i poniższymi poleceniami.

Poniżej początkowa wielkość oraz poziom wykorzystania przestrzeni w pliku bazy danych wraz z średnia fragmentacja dla wszystkich indeksów:

Następnie dropowałem ponad milion rekordów z tabeli Posts oraz Comments w celu za symulowania sporej wolnej przestrzeni w pliku danych.

Wielkość i fragmentacja bez zmian, natomiast poziom wykorzystania przestrzeni w pliku bazy danych wzrósł do 31%.

Wykonanie polecenia SHRINKFILE, które pozwoliło mi przygotować i wypić dobrą kawę, trwało ponad 21 minut:

Przestrzeń wygląda pięknie, niecałe 4% niewykorzystanej przestrzeni. Potwierdziło się to co pisałem wyżej, nastąpiła fragmentacja indeksów, więc uruchomiłem poniższą procedurę Ola Hallengren.

Woooo, po defragmentacji uzyskałem dużo gorszy wynik o jakieś 16% jak przed operacją SHRINKFILE. Natomiast plik rozszerzył się o ponad 40%! Jedyny plus, średnia fragmentacja wróciła do stanu początkowego ale tego można było się spodziewać.

Wykonałem powtórnie te same kroki aby uwiarygodnić wyniki. Spójrz, wygląda to bardzo podobnie.

Dodatkowo, wspomniałem, że SHRINKFILE nie zawszę dokonuję przesunięcia stron z końca. Dzieję się tak wtedy gdy użyjemy argumentu TRUNCATEONLY. Następuje wtedy oddanie do systemu wolne miejsca tylko z końca pliku, do ostatniego wykorzystywanego extendu.

W tym konkretnym labie nie dało to kompletnie nic. Na końcu pliku niema żadnego wolnej strony i nic nie zostało oddane do systemu.

Konkluzja

Shrinkowanie pliku danych jest złe!  Koniec, kropka. Poza przypadkami o których pisałem na samym wstępnie, nie rozważałbym w ogóle zmniejszania pliku danych. Jednak przy takich różnicach pomiędzy used i unused size zadawałbym sobie pytanie dlaczego?

Masz swoje spostrzeżenia lub doświadczenia, napisz w komentarzach.

[Update – 11.07.2017]

Link do wpisu również pokazujący co się dzieje przy SHRINK DATABASE – https://blobeater.blog/2017/07/11/sql-server-rookie-mistakes/

Mateusz Nadobnik

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

read more

2 komentarze

  • Reply

    Adrian Chodkowski

    Czerwiec 30, 2017 at 5:06 pm

    SHRINK sam w sobie jest złą praktyką używaną tylko w ostateczności. W przypadku jednego z moich klientów SHRINK bazy jest wykonywany codziennie w ramach okienka utrzymaniowego na bazach deweloperskich – myślę, że to może być jakieś uzasadnione wykorzystanie tej opcji ponieważ deweloperzy w ciągu całego dnia tworzą ogromną ilość danych i po prostu po krótkim czasie rozmiar bazy może być za duży – oczywiście zawsze są jakieś wady i zalety takiego podejścia.

  • Reply

    Mateusz Nadobnik

    Czerwiec 30, 2017 at 5:15 pm

    Masz racje, przypadek z bazami developerskimi jest jak najbardziej trafny. Po prostu z SHRINKFILE jest tak, że używa się go bardzo rzadko a jak już trzeba to dobrze wiedzieć z czym to się je.