В продолжение статьи «Получение информации о размере базы данных, таблиц и индексов на диске (СУБД MS SQL Server)» я хочу рассказать о том как и с помощью каких административных представлений можно получить сводную информацию о размере базы данных на диске. Непосредственное обращение к административным представлениям дает возможность более гибкой настройки, а также возможность ее использования в различных целях (например, для создания хранимой процедуры, представления или логирования полученных данных).
Информация о размере файлов базы данных
Для получения информации о размере файлов базы данных можно воспользоваться следующими таблицей или представлением: sys.sysfiles, sys.database_files. Приведенные таблица и представление возвращают очень похожие (для нашей цели) данные, основное их различие в том что представление sys.database_files появилось в версии SQL Server 2008 и пришло на смену системной таблице sys.sysfiles, от поддержки которой разработчики планируют отказаться. Между тем, sys.database_files содержит больше информации, но которая в данной статье нам не интересна.
Нижеприведенные запросы возвращают одинаковую информацию:
- Name — Логическое имя файла в базе данных
- Physical_name — Имя файла в операционной системе
- Pages — Размер файла в страницах по 8 КБ
- Size_MB — Размер файла в страницах в МБ
- Descr — Описание типа файла (ROWS — дисковый файл, LOG — журнал транзакций)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-------------------------------------------------------- -- Рекомендуется использовать только до SQL Server 2008 -------------------------------------------------------- SELECT sf.name, sf.filename as physical_name, sf.size as pages, sf.size * 8 / 1024 as size_MB, case when status & 64 = 0 then N'ROWS' else N'LOG' end as descr FROM sys.sysfiles as sf -------------------------------------------------------- -- Возможно начиная с SQL Server 2008 -------------------------------------------------------- SELECT df.name, df.physical_name, df.size as pages, df.size * 8 / 1024 as size_MB, df.type_desc as descr FROM sys.database_files as df |
Информация о распределении пространства в файле журнала транзакций
Немного доработав предыдущий запрос, можно получить информацию о распределении пространства в файле лога транзакций. Воспользуемся функцией FILEPROPERTY, которая возвращает значение свойства файла. Нас интересует свойство «SpaceUsed», которое предоставляет информацию об объеме пространства, используемом файлом.
Нижеприведенный запрос возвращает информацию аналогичную предыдущему запросу, но только по файлу журнала транзакций и дополненную следующими полями:
- Used_MB — Использованное файлом пространство
- Unused_MB — Не использованное файлом пространство
- Used_Percent — Процент использованного пространства к общему размеру файла
- Unused_Percent — Процент не использованного пространства к общему размеру файла
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-------------------------------------------------------- -- Рекомендуется использовать только до SQL Server 2008 -------------------------------------------------------- SELECT sf.name, sf.filename as physical_name, sf.size as pages, sf.size * 8 / 1024 as size_MB, convert (dec (15,0), CAST(FILEPROPERTY(sf.name, 'SpaceUsed') AS INT)) * 8 / 1024 as used_MB, convert (dec (15,0), (sf.size - CAST(FILEPROPERTY(sf.name, 'SpaceUsed') AS INT))) * 8 / 1024 as unused_MB, convert (dec (15,2), convert (dec (15,0), CAST(FILEPROPERTY(sf.name, 'SpaceUsed') AS INT)) / sf.size * 100) as used_Percent, convert (dec (15,2), convert (dec (15,0), (sf.size - CAST(FILEPROPERTY(sf.name, 'SpaceUsed') AS INT))) / sf.size * 100) as unused_Percent, case when status & 64 = 0 then N'ROWS' else N'LOG' end as descr FROM sys.sysfiles as sf WHERE FILEPROPERTY(sf.name, 'IsLogFile') = 1 -------------------------------------------------------- -- Возможно начиная с SQL Server 2008 -------------------------------------------------------- SELECT df.name, df.physical_name, df.size as pages, df.size * 8 / 1024 as size_MB, convert (dec (15,0), CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)) * 8 / 1024 as used_MB, convert (dec (15,0), (df.size - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT))) * 8 / 1024 as unused_MB, convert (dec (15,2), convert (dec (15,0), CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)) / df.size * 100) as used_Percent, convert (dec (15,2), convert (dec (15,0), (df.size - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT))) / df.size * 100) as unused_Percent, df.type_desc as descr FROM sys.database_files as df WHERE FILEPROPERTY(df.name, 'IsLogFile') = 1 |
Информация о распределении пространства в файле базы данных
Получить информацию о распределении пространства в файле базы данных несколько сложнее. Для этого необходимо обратиться сразу к трем системным таблицам:
- sys.partitions — Содержит информацию о секциях всех таблиц и большинства типов индексов
- sys.allocation_units — Содержит информацию о единицах распределения в базе данных
- sys.internal_tables — Содержит информацию о внутренних таблицах
Приведенный ниже запрос возвращает информацию о распределении пространства базы данных по типам пространств:
- totalPages— количество страниц всей базы данных
- reservedPages — количество зарезервированных страниц
- usedPages — количество использованных страниц
- dataPages — количество страниц данных
- indexPages — количество страниц индексов
- unusedPages — количество не использованных страниц
- unallocatedPages — количество нераспределенных страниц
Также запрос выводит данные в:
- Мегабайтах (суффикс «_MB»)
- Страницах базы данных по 8 Кб (без суффикса)
- Проценте относительно всей базы данных (суффикс «_Percent»)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
SELECT -- in MB -- convert (dec (15,2), convert (dec (15,0), sum(totalPages)) * 8 / 1024) as totalPages_MB, convert (dec (15,2), convert (dec (15,0), sum(reservedPages)) * 8 / 1024) as reservedPages_MB, convert (dec (15,2), convert (dec (15,0), sum(usedPages)) * 8 / 1024) as usedPages_MB, convert (dec (15,2), convert (dec (15,0), sum(dataPages)) * 8 / 1024) as dataPages_MB, convert (dec (15,2), convert (dec (15,0), sum(usedPages - dataPages)) * 8 / 1024) as indexPages_MB, convert (dec (15,2), convert (dec (15,0), sum(reservedPages - usedPages)) * 8 / 1024) as unusedPages_MB, convert (dec (15,2), convert (dec (15,0), sum(totalPages - reservedPages)) * 8 / 1024) as unallocatedPages_MB, -- in Pages -- sum(totalPages) as totalPages, sum(reservedPages) as reservedPages, sum(usedPages) as usedPages, sum(dataPages) as dataPages, sum(usedPages - dataPages) as indexPages, sum(reservedPages - usedPages) as unusedPages, sum(totalPages - reservedPages) as unallocatedPages, -- in Percents convert (dec (15,2), convert (dec (15,0), sum(dataPages)) / convert (dec (15,0), sum(totalPages)) * 100) as data_pages_Percent, convert (dec (15,2), convert (dec (15,0), sum(usedPages - dataPages)) / convert (dec (15,0), sum(totalPages)) * 100) as indexPages_Percent, convert (dec (15,2), convert (dec (15,0), sum(reservedPages - usedPages)) / convert (dec (15,0), sum(totalPages)) * 100) as unusedPages_Percent, convert (dec (15,2), convert (dec (15,0), sum(totalPages - reservedPages)) / convert (dec (15,0), sum(totalPages)) * 100) as unallocatedPages_Percent FROM ( SELECT sum(a.total_pages) as reservedPages, sum(a.used_pages) as usedPages, sum(CASE -- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size" WHEN it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) THEN 0 WHEN a.type <> 1 and p.index_id < 2 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) as dataPages, 0 as totalPages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id UNION ALL SELECT 0, 0, 0, df.size FROM sys.database_files as df WHERE df.type_desc = N'ROWS' ) as p |
Сравнение результата со стандартным отчетом «Disk Usage»
Для проверки корректности результатов запросов к административным представления можно сравнить их со стандартным отчетом «Disk Usage». Как видно, мы получили всю необходимую информацию и теперь можем использовать ее в своих целях (например, как было указано в начале статьи, для создания своих хранимых процедур, представлений или логирования информации для последующего ее анализа).