Muszę zmniejszyć swoją bazę danych - właśnie zwolniłem dużo miejsca

To pytanie jest tutaj zadawane w różnych formach, ale sprowadza się do:

Wiem, że zmniejszenie bazy danych jest ryzykowne. W tym przypadku usunąłem tyle danych i nigdy więcej ich nie użyję.

• Jak mogę zmniejszyć swoją bazę danych? Jakie pliki mam zmniejszać?
• Jakie powinny być moje uwagi podczas tego?
• Czy powinienem coś zrobić później?
• Co jeśli jest to duża baza danych? Czy mogę go zmniejszać stopniowo?

Niektóre wstępne zastrzeżenia:

1. Powszechnie znany jako najgorszy praktyce nigdy kurczyć pliku bazy danych lub danych produkcja (Pliki dziennika to kolejny problem, ponieważ kwestia ta opowiada o). Radzę ludziom, aby nie zmniejszali swoich baz danych w postach na blogach, takich jak ten, w których mówię o „odpowiednim dopasowaniu” i dobrym planowaniu. Nie jestem tam sam ( Paul Randal , Brent Ozar , tylko po to, żeby podać jeszcze kilka linków). Zmniejszenie indeksów plików danych lub fragmentów bazy danych, jest powolne i pracochłonne dla zasobów, może powodować drenaż w systemie i jest po prostu złym posunięciem, ogólnie rzecz biorąc
2. W tym przypadku wszyscy wiemy, że istnieje ryzyko, jesteśmy gotowi sobie z tym poradzić, ale uwolniliśmy dużo miejsca, o którym wiemy , że nigdy więcej nie będziemy potrzebować. Tak więc w tym konkretnym przypadku zmniejszanie ma sens jako jedna z naszych opcji.

Jeśli przeczytałeś o obawach i ryzyku i nadal musisz to zmniejszyć, ponieważ zwolniłeś znaczną ilość miejsca, mam nadzieję, że reszta tej odpowiedzi ci pomoże. Ale rozważ ryzyko.

Istnieją dwa główne podejścia, które dwa rozważają tutaj:

1.) Zmniejszenie Tak, wykonaj rzeczywiste zmniejszenie - rozważ użycie DBCC SHRINKFILEzamiast tego DBCC SHRINKDATABASE, masz większą kontrolę nad tym, co się kurczy i jak. To z pewnością spowoduje pewne pogorszenie wydajności - jest to duża operacja wykonująca wiele operacji wejścia / wyjścia. Można potencjalnie uciec z powtarzających się kurczy się o wielkości docelowej, która staje się coraz mniejsza.

To jest przykład „A.)” w powyższym DBCC SHRINKFILElinku. W tym przykładzie plik danych jest zmniejszany do docelowego rozmiaru 7 MB. Ten format jest dobrym sposobem wielokrotnego zmniejszania się, gdy pozwala na to okno przestoju. Zrobiłbym to podczas testowania rozwoju, aby zobaczyć, jak wygląda wydajność i jak nisko / wysoko można zwiększyć, i określić oczekiwany czas produkcji. Jest to operacja online - możesz ją uruchomić, gdy użytkownicy w systemie uzyskują dostęp do bazy danych, której rozmiar jest zmniejszony, ale nastąpi spadek wydajności, prawie gwarantowany. Dlatego najlepiej monitoruj i oglądaj, co robisz na serwerze, wybierz okno przestoju lub okres lżejszej aktywności.

USE YourDatabase;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Zawsze pamiętaj: - za każdym razem, gdy się zmniejszasz, fragmentuj swoje indeksy i powinieneś wykonać przebudowę indeksu, jeśli zamierzasz się zmniejszać w kawałkach przez dłuższy czas. Teraz ponosisz ten koszt za każdym razem, jeśli nie możesz tego zrobić w jednym oknie.

2.) New Database - Ty mógł utworzyć nową bazę danych i migracji do niego. Będziesz musiał utworzyć skrypt dla pustej bazy danych i wszystkich jej kluczy, indeksów, obiektów, procesów, funkcji itp., A następnie migrować do niej dane. Możesz do tego napisać skrypty lub użyć narzędzia takiego jak SQL Data Compare z Red Gate lub innych dostawców z podobnymi narzędziami. Jest to więcej pracy konfiguracyjnej po twojej stronie, więcej prac rozwojowych i testowania, a w zależności od środowiska może również zdmuchnąć okno przestoju, ale jest też opcja do rozważenia.

Kiedy jestem zmuszony zmniejszyć bazę danych Gdyby to było moje środowisko, chciałbym zostawić sporą / dużą ilość białej przestrzeni w pliku danych, ponieważ lubię być wieprzowym dyskiem i być przygotowanym na przyszły / nieoczekiwany wzrost. Byłbym w porządku, oddając spację, jeśli po prostu usunęliśmy większość miejsca, ale nigdy nie ufam tym, którzy mówią „ale nigdy więcej nie odrośnie” i nadal pozostawiają trochę białego miejsca. Trasa, którą prawdopodobnie wybrałbym ( westchnienie) to podejście zmniejszające się, gdybym miał mniejsze okna przestojów i nie chciałem ponosić złożoności tworzenia pustej bazy danych i migracji do niej danych. Więc zmniejszałbym go kilka razy przyrostowo (w oparciu o to, ile razy myślałem, że muszę opierać się na testowaniu w dev i pożądanym rozmiarze. Stopniowo wybierając mniejszy rozmiar pliku), a następnie odbudować indeksy ... A potem „ nigdy nie mów nikomu, że zmniejszyłem swoją bazę danych ;-)

1. Jak mogę zmniejszyć swoją bazę danych? Jakie pliki mam zmniejszać? : Możesz pomniejszyć pliki indywidualnie za pomocą wspomnianego DBCC SHRINKFILEpolecenia. Zależy od serwera, z ilu plików składa się baza danych. Prosta baza danych ma jeden plik bazy danych i jeden plik dziennika transakcji.
2. Jakie powinny być moje uwagi podczas tego?: skurcz wpływa na fragmentację indeksu, patrz trzeci punkt. Pamiętaj również, że nie chcesz zmniejszać pliku bazy danych do rozmiaru, który jest minimalny możliwy, ponieważ w środowisku rzeczywistym i tak będzie się powiększał. Tak więc dostroiłbym rozmiar (w twoim przykładzie dałeś 7 megabajtów) w taki sposób, abyś zostawił 10% -20% wolnego miejsca w pliku bazy danych, ponieważ i tak zostanie ono wypełnione w środowisku produkcyjnym, i możesz zapisz w ten sposób niektóre cykle automatycznego wzrostu. Tak więc rzeczywista liczba wymaga starannego obliczenia. Zwróć też uwagę, że „zwolnienie dużej przestrzeni”, które wykonałeś, spowodowałoby rozdęcie pliku dziennika transakcji nawet bardziej niż miejsce, które zyskałeś w pliku DB. Rzeczywisty zysk przestrzeni, którego możesz doświadczyć, będzie mniejszy niż się spodziewasz matematycznie! Powiedzmy, że matematycznie uwolniłeś 12 koncertów,
3. Czy powinienem coś zrobić później? : Jak wspomniałem wcześniej, chcesz ponownie zindeksować te indeksy, których fragmentacja uległa zniekształceniu w wyniku zmian SHRINK. Nie eksperymentowałem wystarczająco, jeśli chcesz zrobić coś specjalnego ze statystykami zapytań.
4. Co jeśli jest to duża baza danych? Czy mogę go zmniejszać stopniowo? Operację SHRINK można przerwać w dowolnym momencie i można kontynuować później. Jeśli to możliwe, radzę wykonać to w bazie danych off-line. Jednak przerywając i konfigurując, osiągnęłoby to ten sam rozmiar kurczenia się. Teoretycznie możesz zmniejszać się stopniowo, określając mniejszy rozmiar docelowy zamiast 7 megabajtów, ale powiedziałbym, że jeśli wykonujesz go w produkcji, to po prostu daj mu szansę. Jak widać, występują problemy z fragmentacją indeksu i możliwym wzrostem dziennika transakcji. Więc przejdę przez to tylko raz.

Wszyscy wiemy, że nie zaleca się regularnego wykonywania SHRINK. Staram się pomijać wszystkie ostrzeżenia i zastrzeżenia, które prawdopodobnie i tak znasz. Utwórz kopię zapasową i nie rób tego w domu, jeśli to możliwe :)

Bonus: w środowisku replikacji, jeśli wykonasz to w bazie danych wydawcy, nie spowoduje to zmniejszenia baz danych subskrybentów (co może mieć problem z rozmiarem, ponieważ są to wersje Express).

Wreszcie mój skrypt reindex:

USE YourDBName

DECLARE @TbName VARCHAR(255)
DECLARE @FullTbName VARCHAR(255)
DECLARE @IxName VARCHAR(255)
DECLARE myCursor CURSOR FOR
    SELECT OBJECT_NAME(dmi.object_id) AS TableName,i.name AS IndexName
    FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED') dmi
    JOIN  sys.indexes i on dmi.object_id = i.object_id and dmi.index_id = i.index_id
    WHERE avg_fragmentation_in_percent > 30
    ORDER BY avg_fragmentation_in_percent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TbName, @ixName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'dba' AND TABLE_NAME = @TbName)
BEGIN
        SET @FullTbName = 'dba.' + @TbName
        IF (@ixName IS NULL)
        BEGIN
            PRINT 'Reindexing Table ' + @FullTbName
            DBCC DBREINDEX(@FullTbName, '', 0)
        END
        ELSE
        BEGIN
             PRINT 'Reindexing Table ' + @FullTbName + ', Index ' + @IxName
             DBCC DBREINDEX(@FullTbName, @IxName, 0)
        END
    END
    FETCH NEXT FROM myCursor INTO @TbName, @ixName
END
CLOSE myCursor
DEALLOCATE myCursor

Jedyną zmienną w tym jest 14, które można uzyskać wydając polecenie select DB_ID('YourDBName'), a skrypt zakłada, że ​​interesują Cię tylko tabele w schemacie dba. *.

Słyszeliście wszystkie ostrzeżenia o kurczeniu się baz danych i wszystkie one są prawdziwe. Rozdrobni on twoje indeksy i ogólnie zniszczy bazę danych i nie powinien być wykonywany w systemie produkcyjnym.

Ale generalnie robię to co tydzień, gdy przywracam kopię zapasową na stacji roboczej z powodu miejsca na dysku SSD. Pamiętaj, że nie napisałem tego skryptu, ale znalazłem go lata temu. W innych bazach danych [250 GB] utworzyłem pakiet SSIS, który przeniesie potrzebne mi tabele, a następnie odtworzę indeksy dla tak och świeżego indeksu.

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500
-- Set Increment to shrink file by in MB
SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2),
round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000)
DECLARE @SizeMB INT
DECLARE @UsedMB INT

SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB
    ,[StartUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN
    SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

    PRINT 'Start ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    EXEC (@sql)

    PRINT 'Done ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    SELECT @SizeMB = size / 128.
    FROM sysfiles
    WHERE NAME = @DBFileName

    SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

    SELECT [FileSize] = @SizeMB
        ,[UsedSpace] = @UsedMB
        ,[DBFileName] = @DBFileName
END

SELECT [EndFileSize] = @SizeMB
    ,[EndUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

    ,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

Poniższy cytat pochodzi bezpośrednio od firmy Microsoft (dotyczy wersji 2008-2016) i zawiera wskazówki dotyczące tego, czy / kiedy i jak należy użyć tego DBCC SHRINKFILEpolecenia.

https://msdn.microsoft.com/en-us/library/ms189493.aspx

Najlepsze praktyki

Podczas zmniejszania pliku weź pod uwagę następujące informacje:

• Operacja zmniejszania jest najbardziej skuteczna po operacji, która tworzy dużo nieużywanego miejsca, takiej jak operacja obcięcia tabeli lub operacji upuszczenia tabeli.
• Większość baz danych wymaga pewnej wolnej przestrzeni do regularnych codziennych operacji. Jeśli wielokrotnie zmniejszasz bazę danych i zauważysz, że rozmiar bazy ponownie rośnie, oznacza to, że zmniejszona przestrzeń jest wymagana do regularnych operacji. W takich przypadkach wielokrotne zmniejszanie bazy danych to zmarnowana operacja.
• Operacja zmniejszania nie zachowuje stanu fragmentacji indeksów w bazie danych i ogólnie zwiększa stopień fragmentacji do pewnego stopnia. To kolejny powód, aby nie zmniejszać wielokrotnie bazy danych.
• Zmniejsz wiele plików w tej samej bazie danych sekwencyjnie zamiast jednocześnie. Konflikt w tabelach systemowych może powodować opóźnienia z powodu blokowania.