Обслуживание системных баз данных MS SQL Server.

В 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 – дата, записи до которой следует удалить.

Планы обслуживания есть? А настроено ли удаление информации по истории их выполнения из таблиц  msdb.dbo.sysmaintplan_log, msdb.dbo.sysmaintplan_logdetail?. 
Настраиваем:

set @dt=dateadd(dd,-21,getdate())
select @dt
EXECUTE msdb..sp_maintplan_delete_log null,null,@dt

 После этого, выше указанные операции:

-  удаление истории резервного копирования
- очистка журнала Database Mail  
- очистка таблицы истории [msdb].[dbo].[sysssislog]
- очистка таблиц истории выполнения Maitenance Plans.

Мы заворачиваем в ms sql agent задание  и запускаем пару раз в месяц, и в итоге имеем наши компактные системные базы данных:).

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

Возможно что-то пропустил, так что буду рад комментариям.

Будь аккуратны, держите рабочее место в чистоте!:).

upd: 20/01/2017:Добавлена информация о sp_maintplan_delete_log .