Получение сводной информации о размере базы данных на диске с помощью динамических административных представлений

В продолжение статьи «Получение информации о размере базы данных, таблиц и индексов на диске (СУБД 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 — журнал транзакций)

Информация о размере файлов базы данных
Информация о размере файлов базы данных

Информация о распределении пространства в файле журнала транзакций

Немного доработав предыдущий запрос, можно получить информацию о распределении пространства в файле лога транзакций. Воспользуемся функцией FILEPROPERTY, которая возвращает значение свойства файла. Нас интересует свойство «SpaceUsed», которое предоставляет информацию об объеме пространства, используемом файлом.

Нижеприведенный запрос возвращает информацию аналогичную предыдущему запросу, но только по файлу журнала транзакций и дополненную следующими полями:

  • Used_MB — Использованное файлом пространство
  • Unused_MB — Не использованное файлом пространство
  • Used_Percent — Процент использованного пространства к общему размеру файла
  • Unused_Percent — Процент не использованного пространства к общему размеру файла

Информация о распределении пространства в файле журнала транзакций
Информация о распределении пространства в файле журнала транзакций

Информация о распределении пространства в файле базы данных

Получить информацию о распределении пространства в файле базы данных несколько сложнее. Для этого необходимо обратиться сразу к трем системным таблицам:

  • sys.partitions — Содержит информацию о секциях всех таблиц и большинства типов индексов
  • sys.allocation_units — Содержит информацию о единицах распределения в базе данных
  • sys.internal_tables — Содержит информацию о внутренних таблицах

Приведенный ниже запрос возвращает информацию о распределении пространства базы данных по типам пространств:

  • totalPages— количество страниц всей базы данных
  • reservedPages — количество зарезервированных страниц
  • usedPages — количество использованных страниц
  • dataPages — количество страниц данных
  • indexPages — количество страниц индексов
  • unusedPages — количество не использованных страниц
  • unallocatedPages — количество нераспределенных страниц

Также запрос выводит данные в:

  • Мегабайтах (суффикс «_MB»)
  • Страницах базы данных по 8 Кб (без суффикса)
  • Проценте относительно всей базы данных (суффикс «_Percent»)

Информация о распределении пространства в файле базы данных
Информация о распределении пространства в файле базы данных

Сравнение результата со стандартным отчетом «Disk Usage»

Стандартный отчет "Disk usage"
Стандартный отчет «Disk usage»

Для проверки корректности результатов запросов к административным представления можно сравнить их со стандартным отчетом «Disk Usage». Как видно, мы получили всю необходимую информацию и теперь можем использовать ее в своих целях (например, как было указано в начале статьи, для создания своих хранимых процедур, представлений или логирования информации для последующего ее анализа).

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *