Monthly Archives: Styczeń 2013

Jak wymusić zwolnienie miejsca na dysku przez skasowane dane FILESTREAM

FILESTREAM jest mechanizmem silnika bazy danych SQL Server, który pozwala na przechowywanie danych binarnych bezpośrednio w  systemie plików NTFS.   O ile mechanizm ten jest już z nami trochę czasu (został wprowadzony w SQL Server 2008), to premiera SQL Server 2012 ukazała nam jego nową funkcjonalność, a mianowicie FILETABLE.

Więcej na temat FILESTREAM możemy przeczytać w tym miejscu.

Ci z was, którzy nie mieli jeszcze możliwości zapoznać się z FILETABLE mogą przeczytać artykuł mojego autorstwa znajdujący się na portalu wss.pl

Przyznam się, że przed ukazaniem się SQL Server 2012 nie byłem wielkim fanem mechanizmu FILESTREAM. Teraz natomiast – z uwagi na FILETABLE, który z niego korzysta – jak najbardziej TAK.

Używanie mechanizmu FILESTREAM jest obarczone pewnym problemem …

Dane przechowywane jako FILESTREAM są zapisywane bezpośrednio w systemie plików NTFS, a usuwane są za pomocą Garbage Collector-a. Oznacza to, że w przypadku usuwania danych niestety musimy trochę poczekać na odzyskanie miejsca na wolumenie.

Aby zobrazować tę sytuację stworzymy następujące środowisko testowe:

Na początek włączymy FILESTREAM dla instancji serwera. Aby tego dokonać należy użyć narzędzia SQL Server Configuration Manager znajdującego się w menu „Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools”:

FILESTREAM_01

Po włączeniu Configuration Manager-a powinno się zaznaczyć wybraną usługę SQL Server, rozwinąć menu kontekstowe dostępne pod prawym przyciskiem myszki oraz wybrać opcję „Właściwości”. W tym momencie powinno pojawić się okno konfiguracyjne. Przechodzimy w nim do zakładki FILESTREAM i włączamy odpowiednie opcje:

FILESTREAM_02

Kolejnym krokiem jest przejście do SQL Server Mamagement studio i włączenie FILESTREAM wewnątrz usługi SQL:

EXEC sp_configure 'filestream access level', 2
GO
RECONFIGURE
GO

W tym momencie możemy utworzyć bazę testową bazę danych zawierającą katalog FILESTREAM:

CREATE DATABASE FileStreamDatabase
ON PRIMARY (
 NAME = 'FileStreamDatabase_prim',
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamDatabase_prim.mdf',
 SIZE = 250,
 FILEGROWTH = 250
), FILEGROUP FG_FILESTREAM CONTAINS FILESTREAM (
 NAME = 'FileStreamDatabase_filestream',
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamDatabase_filestream'
) LOG ON (
 NAME = 'FileStreamDatabase_log',
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamDatabase_log.ldf',
 SIZE = 250,
 FILEGROWTH = 250
)

Na koniec utworzymy tabelę przechowującą nasze dane binarne jako FILESTREAM:

CREATE TABLE dbo.tblFilestreamTest(
 id int PRIMARY KEY,
 rowID uniqueidentifier UNIQUE NOT NULL ROWGUIDCOL,
 data varbinary(max) FILESTREAM
)

Czas na test !!!

Aby zobrazować problem wstawmy rekord do naszej tabeli:

INSERT dbo.tblFilestreamTest(id, rowID, data)
VALUES(1, NEWID(), CAST('aaaaa' AS varbinary(max)))

Następnie przejdźmy do katalogu przechowującego dane FILESTREAM i sprawdźmy jego zawartość:

FILESTREAM_03

Widzimy tutaj kilka katalogów. Ponieważ nie chciałbym się wdawać w dyskusję na temat w jaki sposób SQL Server dobiera nazwy, to musicie mi uwierzyć na słowo, że dane, które nas interesują znajdują się w folderze „2b131650-c3ba-43bd-94cf-80d17585c157„. Wewnątrz tego folderu będzie znajdować się kolejny katalog (w moim przypadku ma on nazwę „1baaed5d-ede2-4a24-b971-43bfae1f4b0f„), a w nim plik binarny z danymi:

FILESTREAM_04

Na obrazku powyżej widać, że możemy spróbować otworzyć plik za pomocą notatnika i sprawdzić czy aby na pewno zawiera on nasze dane 🙂

Skoro już to zrobiliśmy, to teraz czas na DELETE … albo lepiej … zrobimy UPDATE …

UPDATE dbo.tblFilestreamTest
SET data = CAST('bbb' AS varbinary(max))

Mamy 2 pliki, jeden ze starymi wartościami, a drugi z nowymi !!! Co więcej, oba możemy otworzyć notatnikiem:

FILESTREAM_05

Stary plik będzie usunięty przez Garbage Collector, którego interwał działania nie jest określony.  Przy odpowiednio dużym rozmiarze plików możemy bardzo szybko mieć problemy z wolnym miejscem na wolumenie z danymi.

Rozwiązanie

Do tej pory nie mieliśmy możliwości wymuszenia oczyszczenia dysku z niepotrzebnych wartości. SQL Server 2012 wprowadza nową procedurę systemową o nazwie sp_filestream_force_garbage_collection, która pozwala nam na uruchomienie Garbage Collector w wybranym przez nas momencie:

CHECKPOINT –- musimy to zrobić aby procedura zadziałała 
GO
EXEC sp_filestream_force_garbage_collection @dbName = 'FileStreamDatabase', @filename = 'FileStreamDatabase_filestream'

Na obrazku poniżej widać efekt jej działania:

FILESTREAM_06

Dla niedowiarków przedstawiam jeszcze zrzut ekranu pokazujący, że  w katalogu windowsowym mamy już tylko jeden plik:

FILESTREAM_07

Podsumowanie

Każda kolejna premiera SQL Server wiąże się ze zmianami w silniku baz danych. Zmiany te możemy podzielić na dwa rodzaje. Pierwsze, to te nagłaśniane przez Microsoft i zawarte niemalże w każdej broszurce reklamowej lub stronie www poświęconej temu produktowi. Drugie – „bardziej ciche” i niewystępujące w materiałach promujących. Są one często przydatne w szczególnych sytuacjach lub problemach z serwerem. Ja dzisiaj natknąłem się na procedurę sp_filestream_force_garbage_collection, która może okazać się niezbędna w sytuacji braku wolnego miejsca na dysku spowodowanego rozrostem danych przechowywanych jako FILESTREAM. Mam nadzieję, że i wy po przeczytaniu tego wpisu stwierdziliście, że procedura ta jest warta poświęcenia uwagi 🙂

Reklamy

Rzutowanie z typu money na varchar a utrata precyzji

Dzisiaj szybki w wpis o czymś, co mnie zaskoczyło podczas dzisiejszej pracy. Akurat traf chciał, że od jakiegoś czasu zajmuję się importem danych do SQL Server-a. Ciekawa sprawa na jaką trafiłem, to utrata precyzji podczas rzutowania typów money na varchar. O ile problemowe sytuacje podczas konwersji przytrafiają się, to akurat w „tę stronę” byłem zaskoczony…

Jak wyglądało moje rzutowanie?

Zacznijmy od przykładowego kodu, który będzie wyglądał w ten sposób:

DECLARE @m money = '13245.1234'
SELECT @m AS PrzedRzutowaniem, CAST(@m AS varchar(50)) AS PoRzutowaniu

RZUTOWANIE_01

Na obrazku przedstawionym powyżej widać, że po rzutowaniu na varchar straciłem dwie ostatnie cyfry. Nie przypuszczałem, że zwykły CAST na typ znakowy jest w stanie to zrobić. Rozumiem, gdy utrata precyzji ma miejsce w przypadku CAST z varchar na money … Tu jednak rzutowałem odwrotnie w celu dalszego wyświetlenia wartości.

Rozwiązanie

Jak to zwykle bywa, niezbędne stało się użycie funkcji CONVERT i specjalnie do tego celu stworzonego parametru:

DECLARE @m money = '13245.1234'
SELECT @m AS PrzedRzutowaniem, CONVERT(varchar(50), @m, 2) AS PoRzutowaniu

RZUTOWANIE_02

Użycie „2” w CONVERT-cie daje oczekiwany efekt. Powyższy zrzut ekranu pokazuje, że tym razem jest już OK 🙂

Podsumowanie

Na problemy konwersji każdy z nas natknął się pewnie wiele razy. Standardem jest poświęcenie uwagi podczas rzutowań z typów znakowych na inne.  Jak widać w sytuacji odwrotnej też trzeba mieć się na baczności.