Как сделать расписание sql
Обновлено: 05.07.2024
Данный материал является переводом оригинальной статьи "MSSQLTips : Build and Automate a SQL Server Status Report on Server Restart or Scheduled".
Знаете ли вы, когда перезапускался экземпляр SQL Server? Когда вы управляете множеством экземпляров SQL Server, вы можете не знать, когда перезапускается один из них. Поэтому неплохо было бы настроить отчёт, автоматически отправляемый по почте, чтобы узнавать о ситуации с тем или иным экземпляром SQL Server.
Предварительные требования
Шаг 1 – Создание хранимой процедуры для сбора важной информации
Совет: Используйте тестовый режим для отправки писем себе, потом выключите тестовый режим, для отправки в обычную группу мониторинга.
Вызов для запуска хранимой процедуры в тестовом режиме:
Вызов для запуска хранимой процедуры при выключенном тестовом режиме:
Хранимая процедура имеет много компонентов для проверки разных условий SQL Server.
Это первая часть хранимой процедуры.
Шаг 2 – Какие детали мы хотим видеть
Основные вещи, такие как имя экземпляра. Затем активный узел, где работает экземпляр. Это помогает при управлении кластерами, но не повредит и при одиночном экземпляре.
Вот несколько фрагментов кода, которые можно добавить к хранимой процедуре.
Добавляем информацию по версии SQL Server:
Добавляем время перезапуска SQL Server:
Добавляем свободное место на каждом диске, привязанном к экземпляру:
Добавляем пользователей, созданных за последние X дней:
Добавляем общее количество БД и объём занимаемого места:
Добавляем информацию о самой большой БД:
Добавляем дату старейшего бэкапа (чтобы понять, в порядке ли система резервного копирования):
Добавляем записи из журнала SQL Server Agent:
Добавляем записи из журнала Ошибок SQL Server:
Далее мы можем добавить любые собственные проверки. Следует лишь придерживаться шаблона из секций выше.
Ниже добавляем нижний колонтитул отчета (необходимо для закрытия отчета, а также для добавления END в нижнюю часть хранимой процедуры):
Шаг 3 – Форматирование и доставка Email
Шаг 4 - Сборка
Скопируйте все части, которые вы хотите использовать сверху, в один файл и создайте хранимую процедуру. Кликните здесь для завершения создания хранимой процедуры.
Шаг 5 - Тестирование
Мы можем протестировать хранимую процедуру, используя следующие параметры:
Шаг 6 - Расписание
Чтобы запланировать выполнение хранимой процедуры, мы можем создать Задание SQL Server Agent.
Развертывание путем создания Задания SQL Server Agent:
Один шаг работы - это все, что требуется.
Шаг задания Статус выхода
При установке расписания задания будем ориентироваться, например, на выполнение Задания во время запуска и третий вторник каждого месяца.
Результирующий отчёт
Вот так выглядит пример письма, которое мы можем получить в конечном результате:
Постановка проблемы: Я работаю в пожарной части и занимаюсь статистическим анализом моих данных. Одна проблема состоит в том, чтобы генерировать количество вызовов на обслуживание для каждого часа каждого дня в течение календарного года. Мне нужен стол, который можно присоединить к инцидентам с пожарами, которые происходят каждый день года и каждый час каждого дня. Я надеюсь на следующее (используя военное время)
1 января 2017 00:00:00
1 января 2017 00:00:00
1 января 2017 года 01:00:00
1 января 2017 02:00:00
1 января 2017 03:00:00
1 января 2017 04:00:00
1 января 2017 года 05:00:00
1 января 2017 г. 06:00:00
1 января 2017 года 07:00:00
1 января 2017 года 08:00:00
И т.д. до конца года
31 декабря 2017 года 21:00:00
31 декабря 2017 года 22:00:00
31 декабря 2017 года 23:00:00
Эта таблица позволит мне присоединиться к таблице пожарных происшествий, и я смогу статистически рассчитать количество инцидентов для каждого часа дня и для каждого дня года. Расчетная таблица необходима, потому что в таблице пожарных происшествий есть пробелы. Например; 1 января в 01:00, 02:00 и 03:00 не поступали экстренные вызовы. Поэтому я не могу сделать расчет с использованием таблицы пожарных происшествий, потому что нет данных о том, когда не поступали звонки. Таблица пожарных происшествий с пробелами выглядит следующим образом:
Интервал времени, адрес инцидента
1 января 2017 00:00:00, улица Вязов 123
1 января 2017 04:00:00, улица Дуба 456
1 января 2017 05:00:00, 789 Maple Street
(Обратите внимание, что в часы 0100, 0200 и 0300 не поступают пожарные сигналы. Это пробелы.) Поскольку в данных есть пробелы, в которых нули должны быть рассчитанными средними, необходимыми для распределения Пуассона, отсутствуют. Средние значения неверны.
Желаемый результат . Моя цель - иметь календарь с таблицей часов, чтобы присоединиться к моим пожарным происшествиям, чтобы мой набор результатов вернулся. Вот черновик запроса, который возвращает каждую строку из таблицы календаря и строки из таблицы инцидентов при пожаре, если есть совпадающее значение.
Запрос вернет то, что я надеюсь достичь:
Интервал времени, Количество Инцидентов
1 января 2017 00:00:00, 5
1 января 2017 года 01:00:00, 0
1 января 2017 02:00:00, 0
1 января 2017 03:00:00, 0
1 января 2017 04:00:00, 2
1 января 2017 года 05:00:00, 1
(Обратите внимание, что часы 0100, 0200 и 0300 имеют нулевое количество вызовов. Это то, что я хочу! Теперь я могу создать гистограмму, показывающую, сколько часов было 0 вызовов. Или я могу рассчитать среднее значение, которое учитывает нулевые вызовы для части дня.)
Вопрос:
Как создать таблицу календаря в SQL Server 2012, которая будет иметь каждый день года и каждый час каждого дня. Мой пример снова
Цель: организовать срез бэкапов бухгалтерских баз 1С в первый рабочий день после 25 числа каждого месяца на долгосрочное хранение.
Вам нужно завести в БД таблицу с календарем, где указаны рабочие дни или нужные вам дни. (Если под рабочими днями понимается трудовой календарь.)
А 26 марта 2016 года у Вас рабочий день? Ваша идея с проверкой субботы/воскресенья непонятна. Как минимум этого недостаточно, полагаю вам нужен еще третий "цикл if".
И да, поясните, что же все-таки это за загадочный "цикл из двух if". Сначала думал у Вас опечатка, но после повтора сильно засомневался.
Создан джоб, первый из шагов которого:
Второй шаг в случае успешного выполнения первого:
И расписание запуска 26,27,28 и на всякий 29 числа каждого месяца. Если бэкап с таким именем есть, то он не перезаписывается и возвращается ошибка.
Мне нужно знать, как заставить SQL-запрос работать ежедневно с помощью задания агента SQL Server с минимальными требуемыми параметрами конфигурации.
разверните узел агент SQL Server и щелкните правой кнопкой мыши узел задания в Агенте SQL Server и выберите 'New Job'
на 'New Job' в окне введите название задания и описание на 'General' tab.
выберите 'Steps' в левой части окна и нажмите кнопку 'New' в самом низу.
на 'Steps' окно введите имя шага и выберите базу данных, которую вы хотите запросить бежать против.
вставьте команду T-SQL, которую вы хотите запустить в окно команды, и нажмите 'OK' .
нажмите на кнопку 'Schedule' меню слева от нового окна задания и введите информацию о расписании (например, ежедневно и время).
клик 'OK' - и это должно быть так.
(есть, конечно, другие варианты можно добавить - но я бы сказал, что это минимум вам нужно получить работу, настроенную и запланированную)
Я сделал анимированный GIF шагов в принятом ответе. Это от MSSQL Server 2012
для этого в T-SQL можно использовать следующие системные хранимые процедуры для планирования повседневной работы. Этот пример расписания ежедневно в 1:00. Дополнительные сведения о синтаксисе отдельных хранимых процедур и допустимом диапазоне параметров см. в справке Майкрософт.
использование T-SQL: Моя работа-выполнение хранимой процедуры. Вы можете легко изменить @command для запуска sql.
вот пример кода:
если вы хотите ежедневное резервное копирование / / следующее хранилище сценариев sql в C:\Users\admin\Desktop\DBScript\DBBackUpSQL - . в SQL
откройте Планировщик заданий
создать задачу-> выберите выберите New .
кнопка выберите ежедневный переключатель
нажмите кнопку Action вкладка выберите Создать.
Кнопка Put "C:\Program Files\Microsoft SQL Server0\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC-i "C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql" в поле программа скрипт(убедитесь, что ваш матч путь к файлам и поместите путь с двойными кавычками в поле "пуск - > поиск", и если он найдет, нажмите его и посмотрите, есть ли резервная копия или нет)
-- вышеуказанный путь может быть insted 100 написать 90 "C:\Program Files\Microsoft SQL Server\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC -i "C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"
затем нажмите кнопку OK
скрипт будет выполняться вовремя, который вы выбираете на вкладке триггера ежедневно
Сегодня рассмотрим один из вариантов обслуживания баз 1С в СУБД MS SQL.
Содержание:
1. Немного теории по планам обслуживания
2. Постановка задачи по созданию планов обслуживания
3. Создание плана обслуживания (Полная копия)
4. Создание плана обслуживания (Разностная копия)
5. Создание плана обслуживания (Резервная копия журналов транзакций)
6. Мониторинг планов обслуживания
1. Немного теории по планам обслуживания
Может многие со мой не согласятся, но для меня главной целью использования Планов обслуживания в MS SQL является создание резервных копий. Местные ITишники либо еще не делают резервные копии, либо уже делают, после печальных последствий отсутствия резервных копий. Да, не спорю, Планы обслуживания также нужны для оптимизации БД и выгрузки журналов транзакций, в последнем случаи, если не выполнять выгрузку журналов транзакций, у вас может вырасти база данных и занять все пространство на диске, 1С встанет колом и пользователи не смогут работать с базой, а вам придется выполнять шринк (Shrink) базы, это наверно самое страшное для ITишники после поломки базы и отсутствии резервных копий. Но об шринке (Shrink) поговорим в другой раз.
MS SQL Server поддерживает три модели восстановления:
1) Simple (Простая) — хранится только необходимый для жизни остаток журнала транзакций.
2) Full (Полная) — хранится весь журнал транзакций с момента последнего резервного копирования журнала транзакций.
3) Bulk logged (С неполным протоколированием) — часть операций записываются в очень компактном формате. В остальном идентична Full.
Модель восстановления базы можно посмотреть, в свойствах базы данных, на вкладке Параметры. Там же ее можно поменять. На практике я использую Full (Полная).
MS SQL поддерживает три типа формирования резервных копий:
1) Full (Полная копия)
2) Differential (Дифференциальная копия, Разностная копия)
3) Log (Резервная копия журналов транзакций)
Не путайте понятия: полная модель восстановления и полная резервная копия — разные вещи.
Рассмотрим подробно три типа формирования резервных копий.
1) Полная резервная копия
Позволяет восстановить состояние базы данных на некоторый момент времени. Состоит из копии файлов данных и журнала транзакций на момент завершения формирования резервной копии.
2) Разностная резервная копия
Хранит данных, изменившиеся с момента последней Полной резервной копии. При восстановлении нужно сначала восстановить Полную резервную копию в режиме NORECOVERY, потом можно применить любую из последующих Разностных копий. За счет этого можно значительно снизить объём дискового пространства для хранения резервной копии. Обратите внимание: без предыдущей Полной резервной копии Разностная копия бесполезна. Каждая последующая Разностная копия будет хранить все данные, входящие в предыдущую Разностную резервную копию, сделанную после предыдущей Полной копии. Поэтому каждая следующая Разностная копия больше предыдущих, пока снова не сделать Полную копию. Соответственно для восстановления на какой-то момент времени достаточно последней Полной резервной копии и последней Разностной копии. Промежуточные копии для восстановления не нужны.
3) Резервная копия журналов транзакций
Содержит копию журналов транзакций за некоторый период. Обычно с момента прошлой Резервной копии журналов транзакций до момента формирования текущей Резервной копии журналов транзакций. За счет этого Резервные копии журналов транзакций позволяют (с учетом Полной и Разностной копий) восстановить базу данных на любой момент времени. Резервная копия журналов транзакций высвобождает место в файле журнала транзакций, что позволяет ITишники избавиться от шринка базы данных.
Обратите внимание: набор Резервных копий журналов транзакций по сути бесполезен, если он не является непрерывной цепочкой, причем момент начала последнего успешного Полного или Разностного резервного копирования должен быть внутри периода этой цепочки.
2. Постановка задачи по созданию планов обслуживания
В организации N работают по шестидневке с 8:00 до 17:00. Обед с 12:00 до 13:00.
Имеется в MS SQL база данных с именем Moodle.
Что нужно сделать:
1) Проверить модель восстановления базы данных, должна быть Полная.
2) Создать план обслуживания, который будет создавать Полную резервную копию базы данных каждое воскресение в 17:00. Очищать хранилище от устаревших резервных копий старше 15 дней.
3) Создать план обслуживания, который будет создавать Разностную копию базы данных каждый день в 21:00 кроме воскресения.
4) Создать план обслуживания, который будет создавать Резервную копию журналов транзакций два раза в день, в 12:00 и в 17:00, кроме воскресения.
3. Создание плана обслуживания (Полная копия)
Запускаем SQL Server Management Studio, в Обозревателе объектов проходим по ветке Управление - Планы обслуживания.
Правой кнопкой по пункту Планы обслуживания и в контекстном меню выбираем Создать план обслуживания. Указываем имя, к примеру: Moodle. В открывшемся конструкторе будем создавать вложенные планы обслуживания. щелкните два раза по строке ВложенныйПлан _1
Задайте Имя, Описание и обязательно настройте Расписание выполнения вложенного плана обслуживания: еженедельно в воскресение 17:00:00
Используя Панель элементов создадим первый вложенный план. Достаточно нужный элемент в панели ухватить, перенести на рабочую область и там бросить. Для открытия мастера настройки элемента достаточно два раза щелкнуть по элементу.
Ниже на рисунке представлен результат настройки, который должен у нас получится, но все по порядку.
Размещаем задачу "Проверка целостности базы данных", двойным щелчком мыши открываем диалог настройки задачи, в первую очередь в свойстве Базы данных отмечаем нужную базу, а остальное настраиваем как показано на рисунке. При желании можно посмотреть T-SQL код полученной задачи.
Размещаем следующую задачу "Перестроение индекса" она у нас будет выполнятся только после успешно выполненной предыдущей задачи. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.
Для связи двух задач щелкните по первой задаче "Проверка целостности базы данных" у этой задачи появится стрелка, щелкните по ней и не отпуская соедините со второй задачей "Перестроение индекса". Для изменения значения условия выполнение следующей задачи, щелкните два раза по линии и в открывшемся диалоговом окне выполните необходимые настройки.
Размещаем задачу "Обновление статистики" которая будет выполнятся после завершения предыдущей. Настраиваем эту задачу как на рисунке, не забываем выбрать базу данных.
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'Moodle')
Инструкция DBCC FREEPROCCACHE используется для аккуратной очистки кэша планов. Освобождение кэша планов приводит, например, к тому, что хранимая процедура повторно компилируется, а не используется из кэша.
Размещаем следующую задачу "Резервное копирование базы данных" она у нас будет выполнятся полную резервную копию базы данных. Размещать резервные копии желательно на СХД, если нет, то на другом физическом диске, но ни в коем случае на том же диске где сама база данных, иначе теряется весь смысл резервных копий. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.
Размещаем следующую задачу "Очистка журнала" она у нас будет выполнятся очистку журналов. Настраиваем как показано на рисунке.
Размещаем следующую задачу "Очистка после обслуживания" она у нас будет выполнятся удаление старых файлов резервных копий, так как свойстве Расширение файла указана маска *.*, то удаляются будут все файлы, и полной резервной копии, и разностной, и журнала транзакций. Настраиваем как показано на рисунке.
Обратите внимание, две последние задачи выполняются после выполнения задачи "Резервное копирование базы данных" и самое главное, задачу "Очистка после обслуживания" нужно выполнять только после успешно выполненной задачи "Резервное копирование базы данных". Что бы не получилось, что у вас уже который раз не создаются резервные копии, а вы задачей "Очистка после обслуживания" удаляете последние актуальные копии.
Добавим вложенный план обслуживания, на рисунке ниже красной рамкой выделена данная кнопка и показан результат схемы обслуживания, который должен получится, но все по порядку.
Размещаем две задачи "Проверка целостности базы данных" и "Резервное копирование базы данных", обратите внимание последняя задача выполняется только после успешного завершения предыдущей. Иначе какой смысл делать резервную копию если она не корректна.
На рисунках представлены настройки задачи "Резервное копирование базы данных". Обратите внимание на Тип резервной копии, должен стаять Разностное. И не забудьте указать конкретную базу данных.
На рисунке представлен результат плана обслуживания на 12:00, на 17:00 отличатся ничем не будет, только временем выполнения.
Разместим одну задачу "Резервное копирование базы данных". Обратите внимание на Тип резервной копии, должен стаять Журнал тарнзакций. И не забудьте указать конкретную базу данных.
Откройте Мониторинг активности заданий, в этом мониторинге можно увидеть какие задачи, когда выполнялись, когда следующее выполнение и успешно ли они выполнялись.
Для запуска определенного плана, достаточно в контекстном меню выбрать пункт Запустить задание на шаге.
Сегодня рассмотрели минимальные азы создания планов обслуживания в MS SQL по созданию трех типов резервных копий баз данных: Full (Полная копия), Differential (Дифференциальная копия, Разностная копия) и Log (Резервная копия журналов транзакций).
Читайте также: