Temat: [MS SQL 2008 R2 Express] Rozmiar pliku bazy

Długaśny wstęp, ale wyjaśnię w czym problem:

Dosyć prosta baza w MS SQL Server 2008 R2 Express, kilkanaście tabelek. Dwie tabele przechowywały po dwa pola typu BLOB - jedno jako FILESTREAM, drugie jako varbinary(max) w tabeli. Jako FILESTREAM były składowane duże pliki audio (po kilkadziesiąt MB) lub fotki w oryginalnej rozdzielczości. Natomiast te BLOBy w tabeli to były niewielkie miniatury tych poprzednich - czyli minimalna możliwa mp3, czy też miniatura JPEG. Sens tego taki, że do szybkiego przeglądania danych wystarczają te miniatury, a dopiero potem user ściąga właściwy plik. Sprawowało się świetnie. Ale zaniepokoił mnie rozmiar bazy, bo dobrnąłem pod 7 GB, a to limitu wersji Express zostało nie tak dużo. W plikach (FILESTREAM) siedzi kilkadziesiąt GB, ale to nie wpływa na limit bazy. Ponieważ do bazy powędrowała mniej więcej połowa docelowych danych, to obawiam się, że wkrótce przekroczę ów limit Expressa. O ile podgląd JPEGów (miniatury) zajmuje faktycznie niewiele (kilka KB), o tyle mp3 to nawet po kilkanaście MB - to są często blisko godzinne pliki audio. Pogimnastykowałem się nieco i podglądy z obu tabel przeniosłem do nowych tabelek - gdzie przechowuję je też jako FILESTREAM. Pierwotne kolumny usunąłem, wykonałem Shrink, ale niestety rozmiar podstawowego pliku bazy się nie zmienił. Podglądam rozmiary tabel i te są naprawdę niewielkie, natomiast rozmiar pliku bazy nie zmienił się wiele. Shrink odzyskuje raptem 3%, nadal jestem tuż pod 6GB.

1. Czy to oznacza, że to zajęte wcześniej miejsce zostanie wykorzystane przez dane, jest oznaczone jako wolne? Jeśli tak, to dlaczego Shrink tego nie odzyskuje?

2. Czy w takiej sytuacji backup i restore (pełny, norecovery) całej bazy cokolwiek da?

Dla klarowności ilość danych:

Z tego skorzystałem i właśnie widzę, że tabele mało zajmują. Nie wiem, czy się nie rozjedzie:

Rows ResSize DataSize IndexSize UnusedSize
0 0 KB 0 KB 0 KB 0 KB
0 0 KB 0 KB 0 KB 0 KB
0 0 KB 0 KB 0 KB 0 KB
3318 464 KB 168 KB 280 KB 16 KB
6 32 KB 8 KB 24 KB 0 KB
1260 928 KB 568 KB 152 KB 208 KB
899 496 KB 408 KB 64 KB 24 KB
3 32 KB 8 KB 24 KB 0 KB
7 32 KB 8 KB 24 KB 0 KB
3 32 KB 8 KB 24 KB 0 KB
899 368 KB 192 KB 64 KB 112 KB
1260 448 KB 272 KB 80 KB 96 KB
16 32 KB 8 KB 24 KB 0 KB
381 64 KB 16 KB 48 KB 0 KB
6064 2928 KB 1424 KB 896 KB 608 KB

Jeśli się rozjechało - największa tabela to 6064 wierszy i raptem 3 MB danych.

Rozmiar bazy: 5,788 GB (!)
Total data size - 7,63 MB
Used by data 3,91 MB
Used by indices 2,43 MB
Unused space 1,29 MB
Log 4,5 MB
Dane FILESTREAM - około 400 GB

Pozdrowienia dla wszystkich,
JacekJacek Hajnrych edytował(a) ten post dnia 13.03.12 o godzinie 12:23

Temat: [MS SQL 2008 R2 Express] Rozmiar pliku bazy

OK, ale czy serwer pokazuje ci 5GB wolnego miejsca w plikach?
Wejdź w properties bazy w SSMS, powinno tam być napisane. Jeśli faktycznie masz tyle wolnego miejsca, to nie ma się czym przejmować, baza nie będzie rosła dopóki nie wypełni tej przestrzeni. To, że pliki danych nie chcą się shrinkować może być spowodowane różnymi rzeczami, zdarza się.

Temat: [MS SQL 2008 R2 Express] Rozmiar pliku bazy

shrink odzyskuje tylko miejsce na końcu pliku, jeżeli chcesz odzyskać to co masz w środku przebuduj indeksy zaczynając od klastrowych albo zrób ALTER TABLE tabela REBUILD

Temat: [MS SQL 2008 R2 Express] Rozmiar pliku bazy

Bartosz Ślepowronski:
OK, ale czy serwer pokazuje ci 5GB wolnego miejsca w plikach?
Wejdź w properties bazy w SSMS, powinno tam być napisane. Jeśli faktycznie masz tyle wolnego miejsca, to nie ma się czym przejmować, baza nie będzie rosła dopóki nie wypełni tej przestrzeni. To, że pliki danych nie chcą się shrinkować może być spowodowane różnymi rzeczami, zdarza się.

OK. Czuję się przekonany ;) Pozostawię tak i będę monitorował rozmiar bazy. Myślałem, że shrink przez analogię do defragmentacji poukłada strony tak, żeby można bylo zwolnić dużo zbędnego miejsca. Ale widocznie tajemne siły mu nie pozwalają ;) Aczkolwiek nadal ciekaw jestem, jakie uwarunkowania powodują, że shrink nie zawsze tak działa.

Pozdrowienia,
Jacek

Temat: [MS SQL 2008 R2 Express] Rozmiar pliku bazy

Witam,

DBCC SHRINKFILE jak najbardziej potrafi poprzesuwać strony wewnątrz pliku w celu zwolnienia miejsca, wolne miejsce jest przesuwane na koniec i plik jest obcinany. Oczywiście należy liczyć się z fragmentacją indeksów po takiej operacji.

USE MojaFajnaBaza

GO

DBCC SHRINKFILE (MojFajnyPlikDanych, <rozmiar>)

GO

Ja bym zastosował się do porad Bartka i Roberta, przebuduj indeksy oraz sterty i miej sobie tę przestrzeń zaalokowaną dla bazy.

===
Pozdrawiam,
Daniel