Skip to content

Latest commit

 

History

History
119 lines (75 loc) · 11.3 KB

SQL_Maintenance_Plan.md

File metadata and controls

119 lines (75 loc) · 11.3 KB

НАСТРОЙКА ПЛАНОВ ОБСЛУЖИВАНИЯ ДЛЯ MS SQL

  1. Открываем MSSQL Server Management Studio

  2. Переходим в Управление - Планы обслуживания

*Если их нет, то у вас скорее всего база MSSQL Express, в ней нет планов. Решение либо поставить полноценную но Evilution на 180 дней либо купить

1.1) Если возникает ошибка что "Компонент AgentXP отключен.." то снизу ПКМ по нему и запустить. Также включить его в службах - назначить Автоматический запуск

  • Если также ошибка - то меняем учетную запись для запуска, так как возможно нет прав

  1. Создаем План обслуживания "MyPLAN"

  2. Слева кликаем в "Панель элементов" и перетаскиваем первую задачу "Проверка целостности" в визуальное поле для задач

3.1) Двойной клик по задаче и выбираем нужные базы или ВСЕ и жмем ок. Тут же ставим галочки только "Включить индексы" и "Тлько физические", жмем ОК

  1. Перетягиваем вправо новую задачу "ПЕРЕСТРОЕНИЕ ИНДЕКСА" И делаем связь (зеленую стрелочку) "Проверка целостности" ----> "ПЕРЕСТРОЕНИЕ ИНДЕКСА"

4.1) Двойной клик по "ПЕРЕСТРОЕНИЕ ИНДЕКСА" и указываем БД. Также снизу есть галочка "Фрагментация", ставим и указываем если выше 30%.

*Если меньше 30% - то можно выбрать другую задачу - "РЕОРГАНИЗАЦИЯ индексов"

*ПРосмотреть фрагментацию базы - ПКМ по базе -> Отчеты -> Стандартный отчет -> Физическая статистика индекса

**Если нам нужно найти все фрагментированные индексы - можно сделать через скрипт SIMPLE:

select * from sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent > 30

*Если в найденных индексах page_count слишком маленький, то этот индекс НЕ ПЕРЕСТРОИТСЯ

ПОЛНЫЙ скрипт для отображения фрагментированный индексов с page_count > 100

SELECT TOP 100
       DatbaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       i.type_desc,
       [Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),
       page_count,
       partition_number,
       'alter index [' + i.name + '] on [' + sh.name + '].['+ OBJECT_NAME(s.[object_id]) + '] REBUILD' + case
                                                                                                           when p.data_space_id is not null then ' PARTITION = '+convert(varchar(100),partition_number)
                                                                                                           else ''
                                                                                                         end + ' with(maxdop = 4,  SORT_IN_TEMPDB = on)' [sql]
  FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
  INNER JOIN sys.indexes as i ON s.[object_id] = i.[object_id] AND
                                 s.index_id = i.index_id
  left join sys.partition_schemes as p on i.data_space_id = p.data_space_id
  left join sys.objects o on  s.[object_id] = o.[object_id]
  left join sys.schemas as sh on sh.[schema_id] = o.[schema_id]
  WHERE s.database_id = DB_ID() AND
        i.name IS NOT NULL AND
        OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
        page_count > 100 and
        avg_fragmentation_in_percent > 10
  ORDER BY 4, page_count

Все скрипты по индексам смотреть здесь https://expert.chistov.pro/public/308762/

Скрипт дефрагментации индексов (перестроение и реорганизация) https://gist.github.com/KotegBegemoteg/d224be2683bcb4e4e7a1520f5ff9ee8b

ВАЖНО! *Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он расположен, находится в автономном режиме или предназначен только для чтения. Если указывается ключевое слово ALL, а один или несколько индексов размещены в файловой группе, которая находится в автономном режиме или предназначена только для чтения, то выполнить инструкцию не удастся.

*При перестроении индекса на физическом носителе должно быть достаточно места для хранения двух копий индекса. По завершении перестроейния SQL Server удаляет исходный индекс

  1. Добавляем новую задачу "Обновление статистики". В свойствах выбираем наш объект БД. Все по умолчанию - кликаем ОК. СОЕДИНЯЕМ с предыдущим объектом СТРЕЛОЧКОЙ.

*Но на этот раз достаточно выбрать статус "Завершение" для стрелочки, так как мы знаем что "Проверка целостности" базы прошла успешно.

  1. Новая задача - Очищаем КЭШ! Выбираем в списке задач "Выполнение инструкции T-SQL" и впишем туда DBCC FREEPROCCACHE .И соединяем стрелокой с предыдущей задачей с условием "Завершение"

  2. Новая задача "Резервное копирование базы данных". Сразу ставим СТРЕЛОЧКУ с предыдущего шага (также достаточно Завершение).

В свойствах выбираем типа резервной копии "Полная", указываем БД. И на вкладке ЦЕЛЕВОЙ ОБЪЕКТ указываем место бэкапа. Но сперва лучше выбрать "Создать файл резервной копии для каждой БД" и выбрат ьк примеру диск H:\BAK. И также можно поставить галочку "Создать вложенные каталоги для каждой БД"

На вкладке "Параметры" НЕ использовать сжатие. Поставить галочку "Проверять целостной резервной копии". Кликаем ОК и готово.

  1. Также повторим задачу "Резервное копирование базы данных", СОЕДЕНИМ и поставим СТРЕЛОЧКУ "Сбой" с предыдущим бэкапом, и СДЕЛАЕМ КАК ПОВТОРНУЮ ПОПЫТКУ. ЛИБО еще вариант сделать "Завершение" на стрелочке, и выбрать копирование на NAS как дублирующее. В свойствах на вкладке "Целевой объект" можно выбрать "Присоединять", либо Перезаписать (кому как удобнее)

  2. Добавляем задачу "Очистка журнала". Ставим 1 неделя. Соединяем галочкой Завершение с предыдущ задачей "Резервное копирование базы данных"

  3. Добавим "Очистка после обслуживания" . Соединяем галочкой Завершение с задачей "Резервное копирование базы данных". Выбираем "Удалить из папки файлы с опред расширением" - директория H:\BAK и указать расширение bak. И снизу в Возраст установить галочку "Удалить файлы" и установить 4 недели (по усмотрению)

11) ВАЖНО! СОХРАНЯЕМ ПЛАН - кликаем на дискетку или Ctrl+S

  1. ТЕПЕРЬ нам надо ЗАПЛАНИРОВАТЬ этот план. Кликаем в главном окне плана рядом с Вложенный план по маленькой иконке "календарик". Выставляем Ежедневно в 2:00. Жмем ОК

  2. ЕСЛИ ЕСТЬ ВОЗМОЖНОСТЬ - то СРАЗУ запускаем выполнения плана для тестирования

*Если вышла ошибка, то переходим в Планы обслуживания - выбираем план и ПКМ - Просмотр Журнала.

*Также можем чуть ниже выбрать "Журналы SQL Server"

14) Создаем новый Вложенный план для Журнала Транзакций

  1. Кликаем "Добавление вложенного плана" , называем ЖТР и кликаем на календарик где указываем Ежедневно каждые 30 минут и с 8 утра до 18 вечера

  2. Добавим объект(бэкап) Резервной копирование БД. В свойствах выбираем Журнал Транзакций. Выбираем базу. На вкладке "Целевой Объект" выбираем ОТДЕЛЬНУЮ директорию для бэкапов журналов транзакций (к примеру H:\TRN и расширение trn). На вкладке ПАРАМЕТРЫ указать "Проверять целостность.. ". Сжатие НЕ указывать. Жмем ОК

  3. Добавим "Очистка после обслуживания" Каждые 3 дня, указать директорию наших TRN и расширение trn. И соединяем СТРЕЛОЧКОЙ "Завершение" с предыдущ задачей

ВАЖНО! Только ПОСЛЕ Полного бэкапа имеет смысл создавать бэкап ЖТ

  1. СОХРАНЯЕМ ВЛОЖЕННЫЙ ПЛАН ДЛЯ ЖТ!!!

  2. ЗАПУСКАЕМ и проверяем план

*ОБЯЗАТЕЛЬНО следим за АГЕНТОМ SQL Server, ТАК КАК от него зависит обслуживание этого плана!