Как администратору баз данных мне часто приходится определять размеры баз данных как на уровне базы данных (агрегирование файла журнала и всех файлов данных, составляющих большую базу данных), так и на уровне файлов. Иногда эта информация мне требуется для всех баз данных, а иногда только для одной. Существует ряд возможностей сбора информации, но нет единого метода, который бы открыл все интересующие меня точки метаданных. Почему это важно? Когда мне потребуются эти сведения? Рассмотрим вопросы, интересующие каждого администратора:
- Какие базы данных занимают больше всего места в хранилище данных?
- Используются ли проценты в каких-либо файлах баз данных в качестве меры роста для событий автоматического увеличения?
- Приходилось ли вам встречать ситуации, в которых журналы транзакций имеют неверное значение для размера базы данных?
- Каковы ваши самые крупные журналы транзакций?
- Имеют ли файлы данных для tempdb такой же размер и такие же параметры увеличения размера?
- Приближаюсь ли я к заполнению каких-нибудь файлов данных или файлов журнала?
- Размещены ли файлы данных (или журнала) на неверном диске?
Я могу перейти в среду SQL Server Management Studio (SSMS), раскрыть свойства базы данных и просмотреть сведения для отдельных файлов (размер при создании, логические и физические имена и параметры автоматического увеличения), но я не могу увидеть, насколько «полны» сейчас отдельные файлы.
Я могу запросить sys.database_files и получить похожую информацию, но все же не иметь нужных данных об использовании файлов, а по-прежнему видеть только результаты для единственной базы данных.
Аналогично, я могу запросить системное представление sys.master_files и получить идентичную информацию, представленную в sys.database_files, но вместо результатов только для одной базы данных будет возвращена информация для каждой базы данных на экземпляре SQL Server. По-прежнему отсутствует важный показатель использованного пространства, и это также означает, что нам неизвестно, сколько пространства остается в файле перед событием автоматического увеличения (если оно включено для данного файла).
Вы можете воспользоваться запросом t-sql, чтобы определить степень использования пространства в файле через функцию FILEPROPERTY (), но затем вам необходимо присоединить показатель к результатам других методов, упомянутых выше.
Еще один фактор, который необходимо учитывать: единицы измерения информации, возвращаемой разными средствами, не обязательно совпадают; часть ее представлена в мегабайтах, часть в страницах данных (по 8 Кбайт каждая). Такое рассогласование не упрощает задачу.
Все это заставляет искать единый способ получения информации, полезной администратору баз данных, такой как:
- имя сервера;
- имя базы данных;
- имя файла (как логическое, так и физическое);
- тип файла;
- размер файла, использованное и свободное пространство;
- единицы приращения (процент или фиксированное число Мбайт);
- максимальный размер файла.
Поскольку не существует единого метода объединения этой информации, мне пришлось подготовить хранимую процедуру для ее сбора, и в этой статье я поделюсь своим опытом.
Параметры
Программный код обращается к некоторым из перечисленных выше источников: sys.database_files и функции FILEPROPERTY (). Остальное — чистая математика и преобразование страниц по 8 Кбайт (в некоторых случаях) в мегабайтные единицы. Хранимая процедура принимает два параметра:
- @granularity: d | NULL — 'd' указывает на степень детализации базы данных и объединяет все метрики размеров для каждого файла данных (и отдельно для журнала транзакций) в одну строку;
-
@database_name:
| NULL — если имя базы данных указано, то возвращаются только результаты для этой базы данных. В противном случае возвращаются результаты для всех баз данных на экземпляре.
Возвращаемые результаты
Следующие столбцы возвращаются при указании детализации на уровне базы данных:
- server;
- database_name;
- db_size_mb;
- db_free_mb;
- db_used_mb;
- data_size_mb;
- data_free_mb;
- data_used_mb;
- data_used_pct;
- log_size_mb;
- log_free_mb;
- log_used_mb;
- log_used_pct.
Перечисленные ниже столбцы возвращаются при указании детализации на уровне файлов:
- server;
- database_name;
- file_name;
- physical_name;
- file_type;
- db_size_mb;
- db_free_mb;
- db_used_mb;
- free_space_pct;
- growth_units;
- max_file_size_mb.
Программный код хранимой процедуры
В листинге приводится программный код для этой процедуры. Как и в случае с любыми программами, загружаемыми из Интернета, следует предварительно проверить его содержимое и производительность в непроизводственной среде. Данная команда выполняет только чтение, поэтому она не повлияет на значения и структуру каких-либо данных в ваших компьютерах, но я все равно всегда рекомендую понять программный код, полученный из внешних источников.
Тестовые результаты
Тестовые результаты приведены на экранах в зависимости от четырех возможных комбинаций параметров.
Параметры по умолчанию: детализация на уровне файлов и возврат результатов для всех баз данных (экран 1).
.jpg) |
| Экран 1. Подмножество возвращенных результатов для параметров по умолчанию |
- EXEC dbo.sp_sizing
Параметры: детализация на уровне базы данных и возврат результатов для всех баз данных (экран 2).
.jpg) |
| Экран 2. Подмножество результатов для детализации на уровне базы данных без фильтра для определенной базы данных |
- EXEC dbo.sp_sizing 'd', NULL
Параметры: детализация на уровне базы данных и возврат результатов только для одной базы данных с указанием базы данных (экран 3).
.jpg) |
| Экран 3. Результаты для детализации на уровне базы данных и фильтрации только для базы данных master |
- EXEC dbo.sp_sizing 'd', 'master'
Параметры: детализация на уровне файлов и возвращение результатов только для одной базы данных с указанием базы данных (экран 4).
.jpg) |
| Экран 4. Детализация на уровне файлов с фильтрацией только для базы данных master |
- EXEC dbo.sp_sizing NULL, 'master'
С помощью этого сценария администратор может быстро определить состояние файлов и баз данных, чтобы ответить на разнообразные вопросы, как приведенные в начале статьи, так и другие, которые регулярно возникают перед нами. Надеюсь, мой сценарий будет вам так же полезен, как и мне. В вашем распоряжении появился еще один инструмент, с помощью которого можно быстро получать точные ответы.
USE [master]
GO
CREATE PROCEDURE [dbo].[sp_sizing] @granularity
varchar(1) = NULL, @database_name sysname = NULL AS
/*-------------------------------------------------------------
Хранимая процедура dbo.sp_sizing
Автор Тим Форд, www.sqlcruise.com, www.thesqlagentman.com
Используйте без ограничений, но проверьте программный код
перед выполнением.
Ответственность за выполнение программного кода, загружаемого из Интернета, ложится на пользователя.
-------------------------------------------------------------*/
DECLARE @sql_command VARCHAR(5000)
CREATE TABLE #Results ([server] NVARCHAR(128), [database_name] NVARCHAR(128), [file_name] NVARCHAR(128), [physical_name] NVARCHAR(260),
[file_type] VARCHAR(4), [total_size_mb] INT, [available_space_mb]
INT, [growth_units] VARCHAR(15), [max_file_size_mb] INT)
SELECT @sql_command =
'USE [?] INSERT INTO #Results([server], [database_name], [file_name], [physical_name],
[file_type], [total_size_mb], [available_space_mb],
[growth_units], [max_file_size_mb])
SELECT CONVERT(nvarchar(128), SERVERPROPERTY(''Servername'')), DB_NAME(),
[name] AS [file_name],
physical_name AS [physical_name],
[file_type] =
CASE type
WHEN 0 THEN ''Data'''
+
'WHEN 1 THEN ''Log'''
+
'END,
[total_size_mb] =
CASE ceiling([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END,
[available_space_mb] =
CASE ceiling([size]/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ')
as int) /128)
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ')
as int) /128)
END,
[growth_units] =
CASE [is_percent_growth]
WHEN 1 THEN CAST([growth] AS varchar(20)) + ''%'''
+
'ELSE CAST([growth]/1024*8 AS varchar(20)) + ''Mb'''
+
'END,
[max_file_size_mb] =
CASE [max_size]
WHEN -1 THEN NULL
WHEN 268435456 THEN NULL
ELSE [max_size]/1024*8
END
FROM sys.database_files WITH (NOLOCK)
ORDER BY [file_type], [file_id]'
--Печать команды, применяемой ко всем базам данных
--PRINT @sql_command
--========================================
--ПРИМЕНЕНИЕ КОМАНДЫ К КАЖДОЙ БАЗЕ ДАННЫХ
--========================================
EXEC sp_MSforeachdb @sql_command
--=================================
--ВОЗВРАЩЕНИЕ РЕЗУЛЬТАТОВ
--Если значение @database_name равно NULL:
--=================================
IF @database_name IS NULL
BEGIN
IF @granularity= 'd' /* Database Scope */
BEGIN
SELECT
T.[server], T.[database_name],
T.[total_size_mb] AS [db_size_mb],
T.[available_space_mb] AS [db_free_mb],
T.[used_space_mb] AS [db_used_mb],
D.[total_size_mb] AS [data_size_mb],
D.[available_space_mb] AS [data_free_mb],
D.[used_space_mb] AS [data_used_mb],
CEILING(CAST(D.[available_space_mb] AS decimal(10,1)) / D.[total_size_mb]*100) AS [data_free_pct],
L.[total_size_mb] AS [log_size_mb],
L.[available_space_mb] AS [log_free_mb],
L.[used_space_mb] AS [log_used_mb],
CEILING(CAST(L.[available_space_mb] AS decimal(10,1)) / L.[total_size_mb]*100) AS [log_free_pct]
FROM
(
SELECT [server], [database_name],
SUM([total_size_mb]) AS [total_size_mb],
SUM([available_space_mb]) AS [available_space_mb],
SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]
FROM #Results
GROUP BY [server], [database_name]
) AS T
INNER JOIN
(
SELECT [server],
[database_name],
SUM([total_size_mb]) AS [total_size_mb],
SUM([available_space_mb]) AS [available_space_mb],
SUM([total_size_mb]-[available_space_mb]) AS [used_space_mb]
FROM #Results
WHERE #Results.[file_type] = 'Data'
GROUP BY [server], [database_name]
) AS D ON T.[database_name] =
D.[database_name]
INNER JOIN
(
SELECT [server],
[database_name],
SUM([total_size_mb])
AS [total_size_mb],
SUM([available_space_mb])
AS [available_space_mb],
SUM([total_size_mb]-
[available_space_mb])
AS [used_space_mb]
FROM #Results
WHERE #Results.[file_type] = 'Log'
GROUP BY [server], [database_name]
) AS L ON T.[database_name] =
L.[database_name]
ORDER BY D.[database_name]
END
ELSE /* File Scope */
BEGIN
SELECT [server],
[database_name],
[file_name],
[physical_name],
[file_type],
[total_size_mb] AS [db_size_mb],
[available_space_mb] AS [db_free_mb],
CEILING(CAST([available_space_mb]
AS DECIMAL(10,1)) / [total_size_mb]*100)
AS [free_space_pct],
[growth_units],
[max_file_size_mb] /* AS [Grow Max Size (Mb)] */
FROM #Results
ORDER BY database_name, file_type, [file_name]
END
END
--=================================
--РЕЗУЛЬТАТы ДЛЯ БАЗЫ ДАННЫХ
--Если предоставлен параметр @database_name:
--=================================
ELSE
BEGIN
IF @granularity= 'd' /* Database Scope */
BEGIN
SELECT
T.[server],
T.[database_name],
T.[total_size_mb] AS [db_size_mb],
T.[available_space_mb] AS [db_free_mb],
T.[used_space_mb] AS [db_used_mb],
D.[total_size_mb] AS [data_size_mb],
D.[available_space_mb] AS [data_free_mb],
D.[used_space_mb] AS [data_used_mb],
CEILING(CAST(D.[available_space_mb]
AS DECIMAL(10,1)) / D.[total_size_mb]*100)
AS [data_free_pct],
L.[total_size_mb] AS [log_size_mb],
L.[available_space_mb] AS [log_free_mb],
L.[used_space_mb] AS [log_used_mb],
CEILING(CAST(L.[available_space_mb]
AS DECIMAL(10,1)) / L.[total_size_mb]*100)
AS [log_free_pct]
FROM
(
SELECT [server], [database_name],
SUM([total_size_mb]) AS [total_size_mb],
SUM([available_space_mb]) AS [available_space_mb],
SUM([total_size_mb]-[available_space_mb])
AS [used_space_mb]
FROM #Results
WHERE [database_name] = @database_name
GROUP BY [server], [database_name]
) AS T
INNER JOIN
(
SELECT [server], [database_name],
SUM([total_size_mb])
AS [total_size_mb],
SUM([available_space_mb])
AS [available_space_mb],
SUM([total_size_mb]-[available_
space_mb]) AS [used_space_mb]
FROM #Results
WHERE #Results.[file_type] = 'Data'
AND [database_name] =
@database_name
GROUP BY [server], [database_name]
) AS D ON T.[database_name] =
D.[database_name]
INNER JOIN
(
SELECT [server], [database_name],
SUM([total_size_mb])
AS [total_size_mb],
SUM([available_space_mb])
AS [available_space_mb],
SUM([total_size_mb]-[available_
space_mb]) AS [used_space_mb]
FROM #Results
WHERE #Results.[file_type] = 'Log'
AND [database_name] =
@database_name
GROUP BY [server], [database_name]
) AS L ON T.[database_name] =
L.[database_name]
ORDER BY D.[database_name]
END
ELSE /* File Scope */
BEGIN
SELECT [server],
[database_name],
[file_name],
[physical_name],
[file_type],
[total_size_mb] AS [db_size_mb],
[available_space_mb] AS [db_free_mb],
CEILING(CAST([available_space_mb]
AS DECIMAL(10,1)) / [total_size_mb]*100)
AS [free_space_pct],
[growth_units],
[max_file_size_mb] /* AS [Grow Max Size
(Mb)] */
FROM #Results
WHERE [database_name] = @database_name
ORDER BY file_type, [file_name]
END
END
GO