VN:F [1.7.9_1023]
Rating: 0.0/5 (0 votes cast)

Pomyślałem ostatnio, że fajnie by było, jakbym częściej umieszczał nieco prostsze, acz użyteczne kawałki kodu T-SQL, głównie dedykowane administratorom baz danych. Na dobry początek wykorzystanie często zapominanego i niedocenianego widoku systemowego sys.master_files istniejącego w SQL Server 2005 i nowszych wersjach.

Widok ten jest znakomitym źródłem informacji na temat plików baz danych. Można się dzięki niemu szybko zorientować w takich tematach, jak:

  • czy pliki bazy leżą na tym samym dysku logicznym,
  • jaki jest rozmiar oraz parametry automatycznego powiększania każdego z plików,
  • ile plików jakiego typu wchodzi w skład każdej z baz danych,
  • jaki jest status każdego pliku (OFFLINE / ONLINE, READ_ONLY, etc.),
  • jaki był ostatni log sequence number (LSN) backupu wykonanego na pliku.

Z mojej perspektywy najbardziej przydatna informacja to (oczywiście?) lokalizacja i rozmiar plików (kolumny physical_name oraz size). Rozmiar w kolumnie size liczony jest w 8-kilobajtowych stronach, więc po drodze trzeba wykonać kilka prostych obliczeń, by uzyskać rozmiar pliku na przykład wyrażony w megabajtach. Poniższe zapytanie jest przykładem, jak z widoku sys.master_files można odczytać łączny rozmiar plików każdego rodzaju (log, dane, etc.) dla każdej z baz danych.

SELECT
DB_NAME(database_id) AS database_name,
CONVERT(
numeric(38,2),
SUM(
CASE type
WHEN 0 THEN size * 8192.0 / (1024 * 1024)
ELSE 0
END
)
) AS data_size_mb,
CONVERT(
numeric(38,2),
SUM(
CASE type
WHEN 1 THEN size * 8192.0 / (1024 * 1024)
ELSE 0
END
)
) AS log_size_mb,
CONVERT(
numeric(38,2),
SUM(
CASE type
WHEN 2 THEN size * 8192.0 / (1024 * 1024)
ELSE 0
END
)
) AS filestream_size_mb,
CONVERT(
numeric(38,2),
SUM(
CASE type
WHEN 4 THEN size * 8192.0 / (1024 * 1024)
ELSE 0
END
)
) AS fulltext_size_mb
FROM sys.master_files
GROUP BY database_id
ORDER BY DB_NAME(database_id);

Powyższy skrypt umożliwia natychmiastową odpowiedź na pytania w stylu: “która baza jest największa?”, “która baza używa filestream?”, “gdzie log jest większy niż dane?”.

[EDYCJA 2011-01-26]: Czytelnik o nicku wacio słusznie zwrócił mi uwagę, że dla kontenerów FILESTREAM w widoku sys.master_files kolumna size zawsze przyjmuje wartość 0. Dzięki za czujność. Dla mnie to oczywisty bug, ale na szczęście oczywiste obejście tego problemu jest podane w BOL dla Denali: This field is populated as zero for FILESTREAM containers. Query the sys.database_files catalog view for the actual size of FILESTREAM containers. Mimo to, nadal uważam, że sys.master_files jest ciekawym i przydatnym widokiem :-)