Автор: Вячеслав
В MS SQL Server есть несколько системных баз данных:
master — В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.
Model — Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
Msdb — Используется агентом SQL Server для планирования предупреждений и задач, так же является хранилищем пакетов SSIS, хранилищем информации по резервному копированию.
tempdb — База данных для временных объектов или для промежуточных результирующих наборов.
Resource — База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных.
Задачей обслуживания данных баз данных является возможность восстановления данных баз данных во время их повреждения, неудачного обновления, выхода из строя оборудования и других возможных проблем.
Типичные задачи обслуживания для системных баз данных (за исключением БД TempDb и resource):
— Создание резервной копии баз данных (с глубиной хранения минимум 7 дней)
— Проверка целостности баз данных инструкцией DBCC CHECKDB
Все эти операции можно завернуть в задание sql agent-а и выполнять ежедневно, благо объем данных баз данных обычно небольшой, то и операции проходят довольно быстро, а спокойствия прибавляет).
Но это еще не все, на базе данных msdb я бы хотел подробней остановиться, к типичным вышеуказанным задачам добавляются специфичные задачи обслуживания msdb.
Как известно, в базе данных msdb хранится история резервных копий по базам данных. Теперь представим сервер, у которого баз данных более 50, каждые 10-15 минут проходит создание резервное копирование файла транзакций, какой объем будет таблиц с данной информацией?
На одном месте работы, когда я только туда пришел, на сервере было более 70 баз данных, серверу было более 2,5 лет и информация по резервному копированию никогда не чистилась, в итоге объем базы данных msdb был более 20 Гб!! А это уже совсем другое время и для создания резервной копии баз данных и для проверки целостности самой базы данных, и лишняя дисковая активность, плюс и время восстановления при аварии, в итоге имеем полно минусов, которые мы можем спокойно решить.
Очистка истории резервного копирования осуществляется через процедуру:
sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’
где
[ @oldest_date = ] ‘oldest_date’
Самая ранняя дата, сохраненная в таблицах журнала резервного копирования и восстановления. Аргумент oldest_date имеет тип datetime и не имеет значения по умолчанию
Одну информацию почистили, что еще там хранится?!
Почта. Настроен ли у вас Database Mail и происходит ли отсылка писем, а если еще с вложениями письма?
Вся история по нему так же хранится в базе данных msdb. Для очистки данной истории тоже есть системные процедуры:
sysmail_delete_mailitems_sp [ [ @sent_before = ] ‘sent_before’ ] [ , [ @sent_status = ] ‘sent_status’ ]
где
[ @sent_before = ] ‘sent_before’
Удаляет сообщения электронной почты до даты и времени, указанных аргументом sent_before. Аргумент sent_before имеет тип datetime и не имеет значения по умолчанию. Значение NULL соответствует всем датам.
[ @sent_status = ] ‘sent_status’
Удаляет сообщения электронной почты, тип которых указан аргументом sent_status. Аргумент sent_status имеет тип varchar(8) и не имеет значения по умолчанию. Допустимые значения: sent, unsent, retrying и failed. Значение NULL соответствует всем состояниям.
sysmail_delete_log_sp [ [ @logged_before = ] ‘logged_before’ ] [, [ @event_type = ] ‘event_type’ ]
где:
[ @logged_before = ] ‘logged_before’
Удаляет записи вплоть до даты и времени, указанных в аргументе logged_before. Аргумент logged_before имеет тип datetime и значение по умолчанию NULL. Значение NULL соответствует всем датам.
[ @event_type = ] ‘event_type’
Удаляет журнальные записи определенного типа, заданного аргументом event_type. Аргумент event_type имеет тип varchar(15) и не имеет значения по умолчанию. Допустимые записи: success, warning, error и informational. NULL соответствует всем типам событий.
С почтой мы решили, удалил старую информацию, что еще может быть там?
А есть ли у вас SSIS пакеты и как часто они запускаются? История по их выполнению хранится в таблице [msdb].[dbo].[sysssislog].
Для очистки ее настроена простая инструкция:
delete
FROM [msdb].[dbo].[sysssislog] where starttime<@dt
Где @dt – дата, записи до которой следует удалить.
После этого, выше указанные операции:
— удаление истории резервного копирования
— очистка журнала Database Mail
— очистка таблицы истории [msdb].[dbo].[sysssislog]
Мы заворачиваем в ms sql agent задание и запускаем пару раз в месяц, и в итоге имеем наши компактные системные базы данных:).
Часть данных операций реализуются автоматически во время создания Maintenance Plans, но я их редко использую, поэтому у меня всегда есть скрипт задания обслуживания системных баз данных, который просто разворачиваю на новом сервере при настройке.
Возможно что-то пропустил, так что буду рад комментариям.
Будь аккуратны, держите рабочее место в чистоте!:).
