Проверка места на диске sql

Обновлено: 01.07.2024

Пакет DBMS_SPACE удобен для проверки того, сколько места занято и сколько свободного пространства осталось в различных сегментах, таких как сегменты таблиц, индексов и кластеров. Напомним, что представление словаря данных DBA_FREE_SPACE позволяет находить информацию о свободном месте в табличных пространствах и файлах данных, но не в объектах базы данных. Если пакет DBMS_SPACE не используется, сложно будет узнать, сколько свободного места есть в сегментах, выделенных различным объектам базы данных. Пакет DBMS_SPACE позволяет ответить на следующие вопросы:

Сколько свободного пространства я могу использовать перед тем, как понадобится следующий экстент?
Сколько блоков данных находятся выше маркера максимального уровня заполнения (high-water mark — HWM)?

Представления словаря данных DBA_EXTENTS и DBA_SEGMENTS предоставляют массу информации о месте, выделенном объектам, таким как таблицы и индексы, но вы не сможете узнать из них о том, сколько пространства занято и сколько осталось свободного. Если проанализировать таблицы, то столбец BLOCKS даст HWM — максимальный размер, которого достигала таблица. Однако если таблица подвергалась большому количеству операций вставки и удаления, то HWM не будет показательным индикатором реального использованного пространства. Пакет DBMS_SPACE идеален для нахождения использованного и свободного пространства, оставшегося в объектах.

Пакет DBMS_SPACE включает в себя три основных процедуры: процедура UNUSED_SPACE даст информацию о неиспользованном пространстве в сегменте объекта,процедура FREE_BLOCKS — информацию о количестве свободных блоков в сегменте, а процедура SPACE_USED — подробности об использованном пространстве в блоках.

Давайте внимательнее рассмотрим процедуру UNUSED_SPACE и посмотрим, как ее применять для получения детальной информации о неиспользованном пространстве.Процедура имеет три входных (IN) параметра (четвертый — параметр по умолчанию) и семь выходных (OUT) параметров. В листинге ниже показан вывод, полученный при выполнении процедуры UNUSED_SPACE.

Администратору баз данных важно знать, когда закончится место на диске. И чтобы этого не делать вручную на каждом сервере, лучше этот процесс автоматизировать.

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

Решение

1) создать таблицы для хранения информации:
1.1) для файлов баз данных:

1.2) для логических дисков:

Таблицу с логическими дисками нужно заполнить заранее следующим образом:
название сервера — метка тома (MyServer — C:).

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

Здесь используется системное представление sys.master_files

3) создать хранимую процедуру, которая возвращает информацию по логическому диску:

Данная хранимая процедура была взята из следующей статьи Объём дисков на T-SQL

4) создать хранимую процедуру для сбора информации:
4.1) для файлов баз данных:

4.2) для логических дисков:

5) создать представления для вывода информации:
5.1) для файлов баз данных:

5.2) для логических дисков:

6) создать задание в Агенте и запускать раз в сутки:

7) собрать всю полученную информацию с серверов (также можно через задание агента или другим иным способом)

8) создать хранимую процедуру для формирования отчета и отправки администраторам. Реализовать можно по разному. Поэтому приведу лишь пример:

Данная хранимая процедура формирует HTML-отчет о логических дисках, у которых либо свободного объема осталось меньше 15%, либо свободное место уменьшается свыше 5% за день. Последнее свидетельствует о странной активности записей (кто-то слишком часто и много пишет на данный диск). Первое же может быть следствием одной из нескольких причин:
1) диск пора увеличивать
2) необходимо удалить неиспользуемое занятое место на логическом диске
3) почистить и сжать файлы лога, а также информационных и прочих таблиц.

Результат

В данной статье был рассмотрен пример реализации системы ежедневного автоматического сбора информации о локальных дисках и файлах баз данных. Данная информация позволяет узнать заранее на каком диске свободного места становится все меньше, а также какие файлы баз данных значительно растут. Это позволяет предотвратить случай, когда места на диске не останется, а также выявить причину того, почему какой-то процесс занимает значительную часть места на диске своими записями и т. д.

Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда SQL Server Management Studio предоставляет возможность легко получить информацию о занимаемом БД месте на диске, в том числе: сводную информацию; в разрезе таблиц базы данных; индексов таблиц. Анализ необычных (для системы в целом) данных может выявить ошибки архитектуры и/или ошибки выполнения регламентных операций. Способы получить такую информацию о размере данных на диске будут рассмотрены в данной статье.

SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации о размере базы данных/ее файлов/таблиц/индексов в режиме пользовательского интерфейса.

Отчет содержит общие сведения об использовании места на диске базой данных.

В отчете представлена информация следующего рода:

Общий объем, занятый на диске (Total space reserved)
Место, занятое файлами данных (Data files space reserved)
Место, занятое журналом транзакций (Transaction log space reserved)
Отражает графически процент пространств в составе файлов данных: индексов (index), данных (data), не выделенного (unallocated) и не используемого (unused)
Отражает графически процент примененного (used) и неиспользуемого (unused) пространства в составе журнала транзакций
Выводит записи событий автоматического увеличения (autogrow) и/или сжатия (autoshrink) для базы данных
Выводит информацию о месте на диске, используемом файлами данных

В отчете представлена информация:

Количество записей в таблице базы данных (Records)
Размер зарезервированного пространства на диске (Reserved)
Размер данных на диске (Data)
Общий размер индексов таблицы на диске (Indexes)
Размер не используемого пространства (Unused)

Отчет содержит подробные данные об использовании места на диске индексом и секциями, расположенными в базе данных.

В отчете представлена информация:

Хранимые процедуры

Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.

В процедуре могут быть использованы 2 не обязательных параметра:

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

Результат работы хранимой процедуры sp_spaceused

Динамические административные представления

Помимо вышеприведенных способов, для получения необходимой информации можно обратиться к динамическим административным представлениям. На самом деле, как отчеты, так и хранимая процедура sp_spaceused сама их и использует. Способы получения информации с помощью динамических административных представлений будут рассмотрены в следующих статьях.

Примеры диагностируемых ошибок

Я вручную изменил размер файлов mdf /ndf на большой размер, чтобы избежать операций автозагрузки в базах данных SQL Server. Поскольку файлы больше, на дисковых разделах очень мало свободного места, и системные администраторы продолжают предупреждать меня, что у меня заканчивается свободное пространство.

Поскольку я изменил их размер, в файлах данных много свободного места, но нельзя заметить, что он смотрит на размеры файлов /свободное пространство на диске.

Как я могу контролировать реальное% использования файлов данных? Я бы предпочел использовать счетчики perfmon. Я считаю, что, когда файл действительно исчерпывает пространство, SQL Server не сможет выделить достаточно места и сработает.

3 ответа

Не знаете, почему вы хотите использовать счетчики производительности для этого, когда вы можете получить его из простого запроса. И на самом деле, пока вы можете получить эту информацию о файлах журналов из счетчиков производительности ( Log File(s) Size (KB) / Log File(s) Used Size (KB) ), нет такого счетчика, сколько места используется в файле данных.

Просто для того, чтобы основываться на ответах Аарона и Кина, вы можете сделать это с помощью счетчиков perf, но один из настраиваемые счетчики .

создайте хранимую процедуру, которая будет использовать запрос Aaron, чтобы получить свободное пространство в одном файле или закодировать все файлы и получить интересующее значение min /max.
создать задание, которое будет периодически запускать сохраненный proc

Если вы хотите получить правильное уведомление:

создать оператор /использовать существующий с допустимым адресом электронной почты
создать оповещение на основе этого пользовательского счетчика, который уведомит предыдущего оператора (убедитесь, что вы не заливаете себя электронными письмами - установите задержку между ответами).

у вас есть только 10 настраиваемых счетчиков
у них нет значимого имени
вам нужно иметь громоздкую работу + proc + alert, чтобы иметь приятное изображение

Но они могут использоваться в Perfmon или другом подобном инструменте.

Также есть простые настройки предупреждений для мониторинга размера файла данных:

Читайте также: