Zmniejsz bazę danych poniżej jej początkowego rozmiaru

Mam bazę danych SQL Server 2005 dla deweloperów, która jest 30 GB kopii na żywo. Usunęliśmy niektóre dane, które nie są potrzebne w dev, co zmniejsza zużycie miejsca na dane do 20 GB. Mamy więc około 33% niewykorzystane.

Muszę odzyskać miejsce, co pozwoli nam mieć drugi dev DB na serwerze (w oparciu o wersję skróconą); nie mogę jednak odzyskać miejsca, wykonałem następujące czynności:

• Początkowy rozmiar pliku SMS2_Datato 30 GB.

  DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)

  śledzony przez

  DBCC SHRINKFILE (N'SMS2_Data' , 19500)

Brak przyjemności. Próbowałem wykonać kopię zapasową, tworząc nową bazę danych o niskim rozmiarze początkowym, a następnie przywracając, bez radości, ponieważ rozmiar początkowy zostaje zastąpiony. Próbowałem także:

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000) 

Ten błąd, mówiąc:

MODYFIKACJA PLIKU nie powiodła się. Określony rozmiar jest mniejszy niż aktualny rozmiar.

Próbowałem 20800, a następnie kontynuowałem do 29000 (29 GB) i nadal nie mogę tego zmienić.

Zrobiłem skurcz, a następnie zmieniłem tryb odzyskiwania z FULLna SIMPLEi z powrotem. Brak przyjemności.

Myślałem, że ma to związek z niektórymi TEXTdziedzinami. Mamy około 6 w całym systemie. Więc jako test upuściłem je wszystkie, a następnie zmniejszyłem plik i nadal nie wprowadziłem żadnych zmian.

Pozostała tylko opcja ponownego importowania danych do innej bazy danych. Nie jest to praktyczne, ponieważ należałoby to zrobić na bazie danych na żywo, która niesie ze sobą zbyt duże ryzyko. Częściowo regularnie pobieramy kopię bazy danych na żywo i zastępujemy program / test. Mamy około 500 stołów. Chciałbym zrobić to w sposób, który nie miałby ryzyka eksportu danych do nowej bazy danych.

Próbowałem przenieść dane do innego pliku i skopiowałem wszystkie oprócz 5% danych. To skłoniło mnie do upuszczenia wszystkich kolumn tekstowych.

Serwer działa w trybie zgodności 90, ale jest to SP2. Zrobiłem teraz 3 razy: reindeksuj wszystkie tabele, kopię zapasową bazy danych, zmniejsz plik, zmniejsz bazę danych. Wciąż nie ma radości.

EXECUTE sp_spaceused zwroty:

database_name   database_size   unallocated space
SMS2Tests       31453.94 MB     13903.16 MB

reserved     data         index_size   unused
16545568 KB  10602264 KB  4254360 KB   1688944 KB

Jak niektórzy już wspomnieli, możesz utworzyć nową bazę danych i „skopiować” rzeczy ze starej bazy danych. To byłaby najlepsza opcja dla Ciebie. Zauważyłem jednak, że chcesz to robić dość regularnie. Więc najlepszą opcją jest Redgate Data Compare i Redgate Compare . Oba są częścią pakietu Redgate SqlToolbelt .

Więc co robisz:

1. Utwórz pustą bazę danych o małym rozmiarze początkowym.
2. Użyj narzędzia Redgate Compare, aby skopiować strukturę db, funkcje itp. Ze starej db
3. Użyj narzędzia Redgate Data Compare, aby skopiować dane ze starej bazy danych do nowej
4. Pracujesz na bazie danych deweloperów, a następnie w dowolnym momencie albo po prostu porównujesz dane i regularnie aktualizujesz bazę danych deweloperów, albo jeśli wprowadzasz jakieś zmiany w db, możesz wdrożyć te zmiany za pomocą Redgate Compare, a następnie wykonując Redgate Data Compare.

Zaletą porównywania danych jest to, że po skopiowaniu tych 30 GB danych (możesz to zrobić, zaczynając od niektórych tabel) po pewnym czasie musi po prostu „ponownie porównać” tylko niektóre zmiany, a nie całe 30 GB danych. Co oznacza, że ​​będzie miał o wiele mniejszy wpływ na obie bazy danych niż w przypadku normalnego kopiowania.

Kilka możliwości tutaj.

Po pierwsze, czy korzystasz z SQL 2005 SP3 lub nowszego? Niektórzy zgłaszali problemy z SHRINKFILE we wcześniejszych wersjach (patrz http://www.sqlservercentral.com/Forums/Topic981292-146-6.aspx#bm985164 )

Po drugie, czy możesz sprawdzić, czy baza danych ma ustawiony tryb zgodności 90, a nie tryb 80? Najwyraźniej był to problem z SQL 2000, ale ograniczenie zostało zniesione w SQL 2005. Jeśli baza danych nadal jest ustawiona na tryb zgodności 80, może to nadal stanowić problem.

Po trzecie, może to być problem z danymi LOB (tekst, ntext, varchar (max)). Zobacz doskonały artykuł Paula Randalla tutaj

Zakładam, że rozumiesz, co naprawdę robi SHRINKing i że może to negatywnie wpłynąć na twoją wydajność:

• SHRINK działa poprzez ślepe przenoszenie stron od końca pliku do początku
• Jako taki może strasznie fragmentować indeksy, co może powodować poważne problemy z wydajnością
• Ponieważ operacja ta wymaga dużej ilości danych, kurczenie się może zająć dużo czasu

Zwykle zaleciłbym kontynuację skurczu za pomocą pełnego reindeksu (który odzyska część uwolnionego miejsca), ale nie brzmi to tak, jakbyś był w stanie dojść do tego punktu.

Jeśli spojrzysz na swoje zmniejszone SPID w monitorze aktywności, czy wydaje się, że coś robi? (Zmieniają się liczniki We / Wy i procesora) Czy to jest zablokowane? Jedyną rzeczą, o której mogę myśleć, jest to, że w bazie danych istnieje inna aktywność, która blokuje kurczenie się. Upewnij się, że żadna inna aktywna robota nie korzysta z bazy danych w tym czasie.

Przejście do prostego trybu odzyskiwania podczas tego procesu jest również dobrym pomysłem, aby zapobiec nadmiernemu wzrostowi dziennika.

Jeśli naprawdę chcesz to zrobić:

• ustaw tryb odzyskiwania na prosty
• być świadomym: DBCC SHRINKDATABASE (MyDatabase, TRUNCATEONLY);

SHRINKDATABASE zmniejszy tylko bazę danych (w najlepszym wypadku) do jej MinSize, więc to ci nie pomoże.

Podczas próby zmniejszenia PLIKU za pomocą interfejsu użytkownika SSMS przy użyciu wartości domyślnych używa „DBCC SHRINKFILE (N'MyDB”, 0, TRUNCATEONLY) ”. To polecenie zmniejszy plik (co najwyżej) do ostatniego przydzielonego zakresu.

Jeśli chcesz zmniejszyć plik poniżej MinSize, po prostu zmień parametr DBCC SHRINKFILEz 0 na rozmiar, do którego chcesz spróbować zmniejszyć plik w MB. Liczba niezerowa powie SHRINKFILE, aby zmniejszyć plik do tego rozmiaru, jeśli to możliwe. Tak DBCC SHRINKFILE('MyDB', 300, TRUNCATEONLY)skurczy plik 300MB jeśli baza danych ma miejsca.

Możesz zobaczyć MinSize, uruchamiając to:

DBCC TRACEON(3604)
go
DBCC PAGE('MyDB', 1, 0, 3) with tableresults
go

MinSize w MB jest obliczany w następujący sposób: (MinSize * 8) / 1024

Jeśli masz, powiedzmy, 20003 (mb) danych w bazie danych, wówczas „SIZE = 20000” byłoby zbyt małe. Spróbuj z 21000 lub 25000, sprawdź, czy to działa. (I pamiętaj, 1 GB = 1024 MB.)

Próbować

DBCC SHRINKDATABASE (N'SMS2_Data' , 0)

Użyłem tego w bazie danych SQL 2005, którą mam tutaj, a przydzielone miejsce na plik danych zwiększyło się z 10,2 GB do 3 GB.

Fyi, to jest długi proces i zajęło mi nieco ponad 19 minut dla mojej bazy danych.

Zakładam, że masz jeden plik bazy danych o logicznej nazwie SMS2_Data. W bazie danych znajduje się również jeden lub więcej plików dziennika transakcji.

Masz wyzwanie, którego nie można naprawić w bieżącej kopii bazy danych. Ważną informacją, którą podajesz, jest to, że „oryginalny rozmiar pliku bazy danych wynosi 30 GB”. Niestety, ten plik nie może być zmniejszony mniejszy niż oryginalny rozmiar.

Jak już zauważyłeś, SHRINKDB i SHRINKFILE nie dają ci tego, czego chcesz. Te polecenia są zgodne z regułą zmniejszania mniejszą niż oryginalna. Można więc zmniejszyć bazę danych tylko do pierwotnego rozmiaru i nie mniejszego.

Kopia zapasowa bazy danych i przywracanie do istniejącej, mniejszej bazy danych również nie działa. Podczas przywracania bazy danych pliki bazy danych są przywracane do rozmiarów plików, jakie były podczas tworzenia kopii zapasowej. Ponadto model odzyskiwania (prosty, pełny itp.) Nie ma związku z tym problemem.

I ostatni punkt złych wieści. Możesz rozważyć dodanie innych, mniejszych plików bazy danych do bazy danych, przenieść wszystkie dane z pliku 30 GB, a następnie upuścić. Niestety to również nie zadziała, ponieważ nie można usunąć początkowego pliku z bazy danych.

Najlepszym rozwiązaniem jest więc skopiowanie danych do innej bazy danych. Masz tutaj kilka opcji i być może już je znasz. Pierwszym krokiem jest utworzenie nowej bazy danych o rozmiarze mniejszym niż rozmiar danych. Następnie możesz zwiększyć rozmiar bazy danych do wymaganego rozmiaru.

Możesz rozważyć SSIS jako sposób przesyłania danych z jednej bazy danych do drugiej. Znajdziesz zadanie kopiowania bazy danych, które ci pomoże. Możesz wykonać następujące kroki:

1. Utwórz nową bazę danych o mniejszym rozmiarze niż źródłowa baza danych
2. Skonfiguruj pakiet SSIS za pomocą zadania przesyłania bazy danych. Ustaw zadanie korzystania z transferu online.
3. Uruchom pakiet SSIS.
4. Pakiet SSIS może zmienić rozmiar bazy danych, aby pasował do rozmiaru źródłowej bazy danych. Zmniejsz tę bazę danych, ponieważ ma ona mniejszy oryginalny rozmiar pliku.

Zobacz dodatkowe informacje na temat zadania bazy danych transferu SSIS .

Niektóre nieużywane miejsce w bazie danych jest normalne.
Jeśli masz wiele dużych rekordów (powiedzmy długie łańcuchy), może być dużo nieużywanego miejsca na stronach danych (ponieważ jeden rekord zwykle nie jest dzielony między strony).
Inną sprawą jest współczynnik wypełnienia - początkowo indeksy klastrowe nie są tworzone w 100% pełne, aby uniknąć podziału strony (kosztowna operacja) przy kolejnych wstawieniach.
Jeśli wiele danych zostanie usuniętych z bazy danych, przestrzeń poprzednio zajmowana przez te dane nie zostanie automatycznie odzyskana - pozostanie przydzielona do tabeli.

Spróbuj wywołać DBCC DBREINDEX (table_name, '', 100)każdą tabelę w bazie danych - przebuduje wszystkie indeksy ze współczynnikiem wypełnienia 100%, dzięki czemu dane zostaną umieszczone tak zwięźle, jak to możliwe. Następnie spróbuj ponownie zmniejszyć bazę danych.

Odkryłem, że zmniejszenie bazy danych SQL Server może być kłopotliwe. Czujesz, że musisz zrobić piosenkę i rutynowo tańczyć.

Jest to proces, przez który zwykle przechodzę:

Backup Shrink database Kopia zapasowa dziennika Shrink i pliki bazy danych oddzielnie. Backup Powtarzaj, aż w końcu się zmniejszy.

Musiałem wykonać ten proces kilka razy do trzech razy, aby w końcu zadziałało. Mieliśmy bazę danych o wielkości ponad 68 GB, z czymś 98% niewykorzystanego miejsca. Kilkakrotnie przeszedłem tę procedurę śpiewania i tańca, ale w końcu zmniejszyła się do poniżej 1 GB.

Chciałbym najpierw zmniejszyć początkowy rozmiar pliku mdf do 29 000 MB, a następnie do 28 000, wykrywając zbliżone trafienie.

Nie można oczekiwać zmniejszenia rozmiaru pliku bazy danych o 30% poprzez usunięcie 30% danych.

Możesz oszacować, o ile nieużywanego miejsca w bazie danych

execute sp_spaceused

w kontekście swojej bazy danych (użyj nazwy użytkownika;)
Czy możesz opublikować wynik jej wykonania?

Aktualizacja:
opublikowałem na nim moje powiązane pytanie:

• kompresja, defragmentacja, odzyskiwanie miejsca, baza danych shrinkdatabase vs. plik shrink