Dzisiejszy wpis poświęcam mało docenianemu moim zdaniem zagadnieniu zmiany rozmiaru plików w bazach danych MSSQL. Dlaczego uważam, że temat jest mało doceniany? Sądzę, iż wystarczy odpowiedzieć sobie szczerze na pytanie – jak często kontroluję rozmiary plików swoich baz oraz ilość wolnego miejsca na dysku? Najczęstszą odpowiedzią jaką do tej pory słyszałem da się streścić w prostym – „Po co niby mam to robić? Przecież ustawiłem opcję automatycznego wzrostu”. Może z praktycznego punktu widzenia taka odpowiedź wydaje się słuszna – w końcu wszystkie te bazy danych przecież jakoś działają i nie ma z nimi problemu, ale czy na pewno jest to słuszne podejście?

Moim zdaniem tematem prawie na pewno nie będą zainteresowane osoby, u których wielkości baz danych mierzy się w MB lub przyrost liczby rekordów jest raczej sporadyczny. Sądzę jednak, że w przypadku większych rzędów wielkości zauważalne mogą być już pewne „niuanse”, które na pierwszy rzut oka trudno powiązać właśnie z zagadnieniem zmiany rozmiaru baz danych. W końcu jeśli ktoś nie monitoruje w ogóle zdarzeń tego typu to niby jak miałby tego dokonać?

No dobrze, cały czas straszę, że ignorowanie tego tematu to jakieś zło a nic konkretnego jeszcze o tym nie napisałem. Jakich więc sytuacji powinniśmy się obawiać? Moje propozycje są następujące:
1. Rzecz banalna – jeśli nasza baza nie ma ustawionego autowzrostu a w pliku zaczyna brakować miejsca to tego chyba specjalnie komentować nie trzeba.
2. Rzecz mniej banalna – nasza baza umie powiększać automatycznie swój rozmiar, tylko że ilość wolnego miejsca na dysku zbliża się do końca, co chyba również jest w miarę oczywiste.
3. Rzecz wcale nie taka oczywista – kiedy mamy jeszcze pełno miejsca na dysku, baza umie sama sobie zwiększyć rozmiary plików (lub postępujemy jeszcze lepiej i zwiększamy rozmiar samodzielnie w wybranym przez nas momencie), tylko że dzieje się to w niekontrolowany sposób.

Co mam na myśli pisząc o niekontrolowanym „sposobie”? Tutaj moim zdaniem również można odpowiedzieć całkiem przyzwoitym pytaniem – jeśli już zwiększam rozmiar plików bazy danych (nie ważne czy samodzielnie czy z wykorzystaniem metody autowzrostu) to w jaki sposób dobrałem wielkość tego wzrostu? W miarę przyzwoita odpowiedź powinna brzmieć – oszacowałem sobie „jakoś” na początku (to „jakoś” wcale nie jest takie złe – w końcu jakieś założenia przyjąć trzeba) a potem obserwowałem na ile te szacunki mijają się z rzeczywistością i wprowadziłem korekty. Z moich dotychczasowych obserwacji wynika, że najczęściej wykorzystywane wartości pochodzą wprost „z sufitu” albo są pozostawione po prostu domyślne (zgodnie z zasadą, że skoro domyślne to pewnie dobre i problemów z nimi nie będzie). Co się tutaj może stać? Aby odpowiedzieć na to pytanie musimy zdawać sobie sprawę z tego, że zmiana wielkości plików w bazach danych to nie jest wcale taka banalna operacja. Mówiąc najprostszym językiem kosztuje ona czas. Jeśli więc np. nasza transakcja w efekcie swojego działania będzie wymagała zwiększenia ilości miejsca to czas potrzebny na zwiększenie należy doliczyć do czasu wykonywania tej konkretnej transakcji. Jeśli dodatkowo mamy np. inne oczekujące transakcje to niestety muszą one zaczekać w takim przypadku nieco dłużej (przy czym możliwe jest nawet, że jakaś transakcja się nie doczeka na swoją kolej). Pół biedy jeśli operacja automatycznej zmiany wielkości wykona się raz kiedyś. Może się bowiem zdarzyć, i osobiście miałem okazję to zobaczyć, że w ciągu jednej sekundy operacja zmiany wzrostu wykona się wielokrotnie.

Jak to może wyglądać z punktu widzenia użytkownika? Otóż mogą oni zacząć skarżyć się, że np. czasami wyszukiwanie danych działa wolniej albo pojawia się jakiś enigmatyczny komunikat o błędzie (jeszcze tego by brakowało aby pokazać użytkownikowi „prawdziwy” komunikat o błędzie 😉 ). Spektrum problemów jest oczywiście szersze a zainteresowanym polecam zapoznanie się np. z tym wpisem na stronach Microsoftu.

Jeśli już przekonałem Ciebie czytelniku do zainteresowania się tym tematem to teraz napiszę w jaki sposób ja radzę sobie z monitorowaniem zdarzeń związanych z rozmiarem baz danych.

Jak napisałem o tym w innym wpisie dosyć wygodną formą otrzymywania raportów jest wysyłanie ich sobie jako maile. Oczywiście gdyby ktoś nie lubił tej formy to może sobie np. w tym przypadku codziennie wyklikać w SQL Management Studio odpowiedni raport dla każdej bazy danych (podpowiadam, iż chodzi o standardowy raprot o nazwie „Disk Usage”) 😉

Jeśli chodzi o pobranie informacji o zaalokowanym rozmiarze plików to świetnie nadaje się do tego widok sys.master_files. Widok ten ma jednak pewien mankament – nie podaje ilości aktualnie wykorzystywanego miejsca przez co trudno byłoby nam ocenić czy nadszedł już czas na zwiększenie rozmiaru bazy czy nie. Aby uzyskać takie informacje należy skorzystać dodatkowo z funkcji fileproperty gdzie możemy odczytać wartość SpaceUsed. Niestety i tutaj napotykamy na małą trudność, która polega na tym, iż funkcję tę należy wykonać tylko w kontekście bieżącej bazy danych. Jeśli więc chcemy odczytać dane dla wszystkich baz danych to zaczyna już nam to pachnąć kursorem albo jakimiś dynamicznym zapytaniem. Aby uniknąć użycia kursora proponuję wykorzystać podobno mało udokumentowaną ale całkiem nieźle opisaną na różnych blogach procedurę sp_MSforeachdb, która pozwoli nam wykonać zapytanie dla każdej bazy danych. Fragment kodu, który odczytuje dla nas interesujące informacje może wyglądać następująco

declare @dbSizeInfo table ( 
	dbName nvarchar(250),   
	dbFileName nvarchar(520),
	fileSize int,
	usedSpace int
); 

declare @query nvarchar(max) = N'
	use [' + '?' + '];

	select   
	' + '''' + '?' + '''' + ' as dbName, 
	name as dbFileName,
	size as fileSize,
	fileproperty(name, '+''''+'SpaceUsed'+''''+') as usedSpace
	from sys.database_files'; 
 
INSERT INTO @dbSizeInfo
EXEC sp_MSForEachDB @query;

Efekt naszych powyższych działań możemy teraz połączyć z tabelą sys.master_files, do której dołączymy jeszcze informacje z sys.dm_os_volume_stats gdzie zawarte są m.in. informacje o dostępnej wolnej ilości miejsca na dyskach. W efekcie uzyskamy listę plików wchodzących w skład poszczególnych baz danych, gdzie będziemy mieli m.in. dane o:
– bieżących rozmiarach plików (chodzi o rozmiar zaalokowany),
– bieżącym użyciu miejsca w plikach,
– ilości wolnego miejsca na dysku (gdzie poszczególne pliki fizycznie się znajdują).
Do sortowania wykorzystam sumaryczną wielkość plików dla poszczególnych baz. Uważam, że im baza większa tym bardziej warto na nią zwracać uwagę a więc powinna „bardziej rzucać się w oczy” 😉 Po rozbudowie nasze zapytanie może przybrać następującą formę

declare @dbSizeInfo table ( 
	dbName nvarchar(250),   
	dbFileName nvarchar(520),
	fileSize int,
	usedSpace int
); 

declare @query nvarchar(max) = N'
	use [' + '?' + '];

	select   
	' + '''' + '?' + '''' + ' as dbName, 
	name as dbFileName,
	size as fileSize,
	fileproperty(name, '+''''+'SpaceUsed'+''''+') as usedSpace
	from sys.database_files'; 
 
INSERT INTO @dbSizeInfo
EXEC sp_MSForEachDB @query;

with dbTotalSize as (
	select database_id, 
	sum(size) as size
	from sys.master_files
	group by database_id
)

select dsi.dbName as 'Nazwa bazy',
mf.name as 'Nazwa logiczna pliku',
mf.type_desc as 'Rodzaj pliku',
mf.physical_name as 'Fizyczna nazwa pliku',
cast(dsi.fileSize/ 128.0 as decimal(12,2)) as 'Zaalokowany rozmiar pliku [MB]',
cast(dsi.usedSpace/ 128.0 as decimal(12,2)) as 'Używana ilość miejsca przez plik [MB]',
cast((dsi.fileSize - dsi.usedSpace)/ 128.0 as decimal(12,2)) as 'Ilość wolnego miejsca w pliku [MB]',
cast(100*(dsi.fileSize - dsi.usedSpace)/cast(dsi.fileSize as float) as decimal(12,2)) as 'Ilość wolnego miejsca w pliku [%]',
cast(drv.available_bytes/1048576.0 as decimal(12,2)) as 'Wolne miejsce na dysku [MB]'
from sys.master_files as mf
inner join dbTotalSize as rb on rb.database_id = mf.database_id
left join @dbSizeInfo as dsi on dsi.dbName = DB_NAME(mf.database_id) and dsi.dbFileName = mf.name
cross apply sys.dm_os_volume_stats(mf.database_id, mf.file_id) as drv
order by rb.size desc, mf.type_desc desc, mf.name asc;

Do swojego zestawienia dodaję jeszcze na wszelki wypadek informacje na temat zdarzeń Auto Grow oraz Auto Shrink. Dzięki temu mam wiedzę o tym czy i jak często takie zdarzenia mają miejsce. Punktem wyjściowym mojego skryptu dla tego fragmentu zestawienia jest następujący kod

select te.name,
t.DatabaseName, 
t.FileName,
cast(cast(t.IntegerData as float) / 128 as decimal(10,2)) as 'Rozmiar [MB]',
t.StartTime,
t.EndTime,
(t.Duration / 1000) as 'Czas trwania [ms]',
t.SessionLoginName
from sys.fn_trace_gettable(convert(varchar(150), (	select top 1 f.[value] 
													from sys.fn_trace_getinfo(null) f 
													where f.property = 2 )), default) as t
join sys.trace_events as te on T.EventClass = te.trace_event_id
where te.name = 'Data File Auto Grow'
or te.name = 'Log File Auto Grow'
or te.name = 'Data File Auto Shrink'
or te.name = 'Log File Auto Shrink'
order by t.StartTime desc

W tym miejscu wspomnę, iż dane do tego zestawienia są czytane z plików Trace więc należy pilnować, który plik odczytujemy. W proponowanym przeze mnie rozwiązaniu wykorzystujemy tylko najnowszy, więc jeśli wczoraj Twój raport pokazywał jakieś dane a dzisiaj jest pusty to najprawdopodobniej w międzyczasie miało miejsce utworzenie nowego pliku Trace. Czy warto się tym przejmować? Wg mnie nie – obserwowanie dynamiki zmian rozmiarów jest wg mnie na tyle długotrwałym procesem, że nawet brak danych z jednego dnia specjalnie tego nie zaburzy (no chyba, że akurat chodzi o dzień np. generowania jakichś rozliczeń gdzie spodziewamy się szczególnej aktywności, którą chcemy sobie poobserwować 😉 ). Zawsze też można rozwinąć sobie zapytanie tak aby branych pod uwagę było więcej plików niż tylko „aktualny”. Wszystko zależy tutaj od naszych upodobań i potrzeb w tym zakresie.

Poza powyższym zestawieniem w codziennym raporcie podaję sobie jeszcze jedno podobne, które zawiera listę baz, w których zdarzenie zmiany rozmiaru wystąpiło więcej niż jeden raz w ciągu dnia (powiedzmy, że to takie przypadki, którymi warto się dodatkowo przyjrzeć). Zapytanie dla tego dodatkowego zestawienia jest modyfikacją powyższego i wygląda następująco

select te.name,
t.DatabaseName, 
t.FileName,
cast(max(t.StartTime) as Date) as 'Data',
count(*) as 'Ilość'
from sys.fn_trace_gettable(convert(varchar(150), (	select top 1 f.[value] 
													from sys.fn_trace_getinfo(null) f 
													where f.property = 2 )), default) as t
join sys.trace_events as te on T.EventClass = te.trace_event_id
where te.name = 'Data File Auto Grow'
or te.name = 'Log File Auto Grow'
or te.name = 'Data File Auto Shrink'
or te.name = 'Log File Auto Shrink'
group by t.DatabaseName, t.FileName, te.name, year(t.StartTime), month(t.StartTime), day(t.StartTime)
having count(*) > 1
order by max(t.StartTime) desc

Wszystkie cząstkowe zestawienia mogę opakować sobie teraz w kod HTML podobnie jak opisałem to tutaj. Kod zaprezentowany poniżej pokazuje w jaki sposób można przygotować kompletną strukturę HTML włącznie ze stylami CSS. Na potrzeby niniejszego wpisu zastosowałem stosunkowo proste stylowanie ale chyba sam przyznasz drogi czytelniku, że jest tutaj całkiem niezły potencjał do dostosowania wyglądu zestawienia do własnych upodobań 😉

declare @dbSizeInfo table ( 
	dbName nvarchar(250),   
	dbFileName nvarchar(520),
	fileSize int,
	usedSpace int
); 

declare @query nvarchar(max) = N'
	use [' + '?' + '];

	select   
	' + '''' + '?' + '''' + ' as dbName, 
	name as dbFileName,
	size as fileSize,
	fileproperty(name, '+''''+'SpaceUsed'+''''+') as usedSpace
	from sys.database_files'; 
 
INSERT INTO @dbSizeInfo
EXEC sp_MSForEachDB @query;

declare @zestawienieRozmiary nvarchar(max);

with dbTotalSize as (
	select database_id, 
	sum(size) as size
	from sys.master_files
	group by database_id
)
select @zestawienieRozmiary = cast((
	select
		(	select 'Nazwa bazy' as 'th',
			'Nazwa logiczna pliku' as 'th',
			'Rodzaj pliku' as 'th',
			'Fizyczna nazwa pliku' as 'th',
			'Zaalokowany rozmiar pliku [MB]' as 'th',
			'Używana ilość miejsca przez plik [MB]' as 'th',
			'Ilość wolnego miejsca w pliku [MB]' as 'th',
			'Ilość wolnego miejsca w pliku [%]' as 'th',
			'Wolne miejsce na dysku [MB]' as 'th'
			for xml raw('tr'), elements, type),
		coalesce(	(	select dsi.dbName as 'td',
						mf.name as 'td',
						mf.type_desc as 'td',
						mf.physical_name as 'td',
						cast(dsi.fileSize/ 128.0 as decimal(12,2)) as 'td',
						cast(dsi.usedSpace/ 128.0 as decimal(12,2)) as 'td',
						cast((dsi.fileSize - dsi.usedSpace)/ 128.0 as decimal(12,2)) as 'td',
						cast(100*(dsi.fileSize - dsi.usedSpace)/cast(dsi.fileSize as float) as decimal(12,2)) as 'td',
						cast(drv.available_bytes/1048576.0 as decimal(12,2)) as 'td'
						from sys.master_files as mf
						inner join dbTotalSize as rb on rb.database_id = mf.database_id
						left join @dbSizeInfo as dsi on dsi.dbName = DB_NAME(mf.database_id) and dsi.dbFileName = mf.name
						cross apply sys.dm_os_volume_stats(mf.database_id, mf.file_id) as drv
						order by rb.size desc, mf.type_desc desc, mf.name asc
						for xml raw('tr'), elements, type),
					(	select (	select 9 as '@colspan', 
									'Brak danych' as '*' 
									for xml path('td'), type)
						for xml path('tr'), type))
	for xml raw('table'), elements)
as nvarchar(max));

declare @zestawienieAutoZmiany nvarchar(max);

select @zestawienieAutoZmiany = cast((
	select
		(	select 'Zdarzenie' as 'th',
			'Nazwa bazy' as 'th',
			'Nazwa logiczna pliku' as 'th',
			'Wielkość zmiany [MB]' as 'th',
			'Czas rozpoczęcia' as 'th',
			'Czas zakończenia' as 'th',
			'Czas trwania [ms]' as 'th',
			'Login' as 'th'
			for xml raw('tr'), elements, type),
		coalesce(	(	select te.name as 'td',
						t.DatabaseName as 'td', 
						t.FileName as 'td',
						cast(cast(t.IntegerData as float) / 128 as decimal(10,2)) as 'td',
						t.StartTime as 'td',
						t.EndTime as 'td',
						(t.Duration / 1000) as 'td',
						t.SessionLoginName as 'td'
						from sys.fn_trace_gettable(convert(varchar(150), (	select top 1 f.[value] 
																			from sys.fn_trace_getinfo(null) f 
																			where f.property = 2 )), default) as t
						join sys.trace_events as te on T.EventClass = te.trace_event_id
						where te.name = 'Data File Auto Grow'
						or te.name = 'Log File Auto Grow'
						or te.name = 'Data File Auto Shrink'
						or te.name = 'Log File Auto Shrink'
						order by t.StartTime desc
						for xml raw('tr'), elements, type),
					(	select (	select 9 as '@colspan', 
									'Brak danych' as '*' 
									for xml path('td'), type)
						for xml path('tr'), type))
	for xml raw('table'), elements)
as nvarchar(max));


declare @zestawienieIloscAutoZmian nvarchar(max);

select @zestawienieIloscAutoZmian = cast((
	select
		(	select 'Nazwa bazy' as 'th',
			'Nazwa logiczna pliku' as 'th',
			'Wielkość zmiany [MB]' as 'th',
			'Data' as 'th',
			'Ilość' as 'th'			
			for xml raw('tr'), elements, type),
		coalesce(	(	select te.name as 'td',
						t.DatabaseName as 'td', 
						t.FileName as 'td',
						cast(max(t.StartTime) as Date) as 'td',
						count(*) as 'td'
						from sys.fn_trace_gettable(convert(varchar(150), (	select top 1 f.[value] 
																			from sys.fn_trace_getinfo(null) f 
																			where f.property = 2 )), default) as t
						join sys.trace_events as te on T.EventClass = te.trace_event_id
						where te.name = 'Data File Auto Grow'
						or te.name = 'Log File Auto Grow'
						or te.name = 'Data File Auto Shrink'
						or te.name = 'Log File Auto Shrink'
						group by t.DatabaseName, t.FileName, te.name, year(t.StartTime), month(t.StartTime), day(t.StartTime)
						having count(*) > 1
						order by max(t.StartTime) desc
						for xml raw('tr'), elements, type),
					(	select (	select 9 as '@colspan', 
									'Brak danych' as '*' 
									for xml path('td'), type)
						for xml path('tr'), type))
	for xml raw('table'), elements)
as nvarchar(max));


declare @zestawienie nvarchar(max) = N'<!doctype html>
<html>
	<head>
		<style>
			body { font-size: 11px; }
			table { border-collapse: collapse; width: 100%; }
			table, td, th { border: 1px solid; }
			th, td { text-align: center; vertical-align: middle; }
			th { background-color: #eee; }
			.zestawienie { margin-bottom: 30px; }
		</style>
	</head>
	<body>
		<div class="zestawienie">
			<h2>Rozmiary baz danych</h2>
			' + @zestawienieRozmiary + '
		</div>
		<div class="zestawienie">
			<h2>Zdarzenia automatycznej zmiany rozmiaru baz danych</h2>
			' + @zestawienieAutoZmiany + '
		</div>
		<div class="zestawienie">
			<h2>Zdarzenia automatycznej zmiany rozmiaru baz danych występujących więcej niż raz dziennie</h2>
			' + @zestawienieIloscAutoZmian + '
		</div>
	</body>
</html>';

Cały powyższy kod można teraz jeszcze uzupełnić o część wysyłającą zawartość zestawienia na adres email, opakować to w procedurę i podłączyć do harmonogramu jako job. Warto również zwrócić uwagę na kodowanie znaków – można dodać w razie potrzeby odpowiedni znacznik w części head naszej struktury HTML jak np.

<meta http-equiv="Content-Type" content="text/html; charset=Windows-1250" />

Na koniec jeszcze kilka słów wyjaśnienia. W tym wpisie nie podawałem wyniku działania żadnego ze skryptów z dwóch powodów. Pierwszym jest chęć ukrycia informacji o moich bazach 😉 Zanim ktoś posądzi mnie teraz o lenistwo, że przecież mogłem „zmanipulować” jakoś rezultaty albo je ocenzurować szybko podaję drugi powód – chciałbym zachęcić Ciebie czytelniku do uruchomienia tego skryptu na Twoim serwerze abyś miał okazję samemu przekonać się w jakim stanie są Twoje bazy (szczególnie jeśli wcześniej tego nie robiłeś) 😉