SQL SERVER AGENT
Example: We create Job on CustTable which delete records every minute from Customer table.
Steps for creating job:
- SQL server → Expand sql server agent → right click on job folder → New job.
2.Fill the General Information.
3.Create New steps.
4.Create New Job Schedules: Schedules→ new
- After creating Job it will display in Job Folder.
Job Activity Monitor:
- It is allows you to view the job activity table by using SQL Server Management Studio.
- You can view all jobs, you can define filters to limit the number of jobs displayed.
- You can also sort the job information by clicking on a column heading in the Agent Job Activity grid.
- For example, when you select the Last Run column heading, you can view the jobs in the order that they were last run. Clicking the column heading again toggles the jobs in ascending and descending order based on their last run date.
Using the Job Activity Monitor you can perform the following tasks:
- Start and stop jobs.
- View job properties.
- View the history for a specific job.
- Refresh the information in the Agent Job Activity grid manually or set an automatic refresh interval by clicking View refresh settings.
- After Job executed All records delete from table.
- For different options right click on Specific Job.
If you are new to SQL refer Below video for better Understanding.
3.5. SQL Server Agent — Часть 1
В состав сервера MS SQL Server входит сервис SQL Server Agent, который состоит из сообщений, операторов и работ. Наибольший интерес программистов и администраторов вызывают работы, поэтому этой теме мы уделим достаточно подробное внимание.
Работа администратора очень часто связана с выполнением однообразных задач, что превращает рабочий день в серые будни. Для меня это самое сложное, поэтому многократно выполняемые задачи я стремлюсь автоматизировать. У MS SQL Server есть достаточно мощное средство автоматизации – работ (job). Работы – это набор определенных действий (например, SQL запросов), которые могут выполняться сервером автоматически в определенное время с помощью планировщика (Schedule) или запускаться администратором вручную.
Ярким примером задач администратора, которые могут вызывать скуку, является обслуживание баз данных, о чем мы будем достаточно много говорить в главе 4. Например, можно запрограммировать сервер так, чтобы он каждый день в конце рабочего дня создавал резервную копию базы данных.
Работы состоят из шагов, которые последовательно выполняются сервером MS SQL Server. Выполнение каждого последующего шага может зависеть от результата предыдущего. Таким образом, можно строить определенную логику задач.
Вы должны учитывать, что работы выполняются не самим сервисом MS SQL Server, а сервисом SQL Server Agent, который входит в поставку MS SQL Server. Поэтому, убедитесь, что этот сервис работает, иначе работы не смогут выполняться по расписанию.
Помимо этого, если сервис обращается к удаленным серверам по сети, то SQL Server Agent должен работать под реальной учетной записью, а не под системной. Чтобы изменить имя пользователя, с правами, которыми работает сервис, запустите оснастку Сервисы (Пуск/Панель управления/Администрирование/Сервисы). Перед вами откроется окно, как на рисунке 3.1. Найдите строку с именем сервиса SQLSERVERAGENT и дважды щелкните по ней. Перейдите на закладку «Вход в систему» (Log on) и укажите реальную учетную запись пользователя, который существует в системе и обладает правами на необходимые ресурсы вашего компьютера и удаленного сервера, к которому будет происходить подключение по сети. Если сервис SQL Server Agent будет работать с правами системного аккаунта, то у него не хватит прав на подключение к удаленной системе, потому что системный аккаунт не имеет имени пользователя и пароля, необходимых для аутентификации.
3.5.1. Добавление работы
Начнем с добавления записей. Для этого используется хранимая процедура sp_add_job, которая выглядит следующим образом:
Рассмотрим параметры, которые передаются данной процедуре:
- @job_name – имя работы, которое должно быть уникальным и не может содержать символ процента;
- @enabled – индикатор активности работы. Если параметр равен 1, то работа активна и может выполняться из планировщика задач. Если указать 0, то работа не может выполняться автоматически, запуск доступен только вручную;
- @description – текстовое описание работы;
- @start_step_id – идентификатор работы, которая должна выполняться первой, по умолчанию используется число 1;
- @category_name – имя категории;
- @category_id – идентификатор категории. Этот параметр может использоваться в определенных языках программирования;
- @owner_login_name – имя пользователя, который будет являться владельцем работы. Если ничего не указано, то владельцем будет текущий пользователь;
- @notify_level_eventlog – какие сообщения должны сохраняться в журнале событий Windows. Здесь можно указать одно из следующих значений:
- 0 – никакие сообщения;
- 1 – сообщения об удачном завершении;
- 2 – сообщения об ошибках (значение по умолчанию);
- 3 – все сообщения.
Прежде чем использовать процедуру, необходимо отметить, что она принадлежит базе данных msdb, поэтому необходимо подключиться именно к этой базе.
Есть еще одно ограничение – вы должны указывать имена только реально существующих в базе данных операторов, поэтому посмотрим на пример без указания операторов:
3.5.2. Управление операторами
Оператор – это описание человека, который должен получать сообщения сервера MS SQL Server и сообщения о ходе выполнения работы. Для создания оператора используется процедура sp_add_operator, которая выглядит следующим образом:
Рассмотрим параметры этой процедуры:
- @name – имя оператора;
- @enabled – если параметр равен 1, то оператор активен, а если указать 0, то оператор не доступен и не должен получать сообщений;
- @email_address – электронный адрес (e-mail) оператора. Если значение этого параметра является физическим адресом SMTP:flenov@mail.ru, а не псевдонимом flenov, то он должен быть заключен в квадратные скобки [SMTP:flenov@mail.ru]. Дело в том, что по умолчанию MS SQL Server использует почтовый сервис Exchange Service, который использует свою систему именования ящиков;
- @pager_address – адрес Интернет пейджера;
- @weekday_pager_start_time – время, после которого можно отправлять сообщения на пейджер в рабочий день. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
- @weekday_pager_end_time – время, до которого можно отправлять сообщения на пейджер в рабочий день. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
- @saturday_pager_start_time и @sunday_pager_start_time – время, после которого можно отправлять сообщения на пейджер в субботу и в воскресенье соответственно. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
- @saturday_pager_end_time и @sunday_pager_end_time – время, до которого можно отправлять сообщения на пейджер в субботу и в воскресенье соответственно. Время указывается в формате ЧЧММСС, например, число 100100 соответствует 10 часам, 01 минуте и 00 секундам;
- @pager_days – этот параметр определяет дни, в которые оператор доступен для получения сообщений на пейджер. Здесь можно указать одно из следующих значений или сумму из нескольких чисел:
- 0 – никогда (это значение по умолчанию);
- 1 – по воскресеньям;
- 2 – по понедельникам;
- 4- по вторникам;
- 8 – по средам;
- 16- по четвергам;
- 32 – по пятницам;
- 64 – по субботам.
Как указать, что пользователь доступен с понедельника по пятницу? Для этого складываем соответствующие числа 2+4+8+16+32. В результате мы получим 62 и именно это значение необходимо указать в параметре @pager_days.
- @netsend_address – сетевой адрес оператора (имя компьютера), на который нужно отправлять сообщения;
- @category_name – имя категории сообщений.
Посмотрим, как можно создать оператора, который будет получать сообщения на e-mail адрес:
Следующий пример создает оператора, который может получать e-mail и NET SEND сообщения:
Теперь посмотрим, как можно создать работу, в которой сообщения о статусе выполнения работы передаются операторам:
Если хотя бы один из операторов, указанных в примере не будет существовать в базе данных, выполнение процедуры завершиться неудачей.
Изменение оператора
Для изменения параметров оператора используется процедура sp_update_operator, которая выглядит так:
Параметры процедуры изменения оператора такие же, как и при создании, поэтому не будем тратить время на рассмотрения оператора, а лучше посмотрим его работу на практике. Следующий пример изменяет e-mail и сетевой адрес:
Указание имени оператора является обязательным, потому что процедура должна знать, какого именно оператора нужно обновлять.
Следующий пример делает оператора не активным, после чего он не будет получать информационные сообщения:
Изменяется только параметр @enabled, а все остальные не изменяются и сохраняют свои значения.
Информация об операторе
Чтобы убедится в том, что изменения прошли успешно, можно воспользоваться процедурой sp_help_operator, которая выводит информацию об операторе. В качестве параметра @operator_name нужно передать имя интересующего вас оператора, например, так:
Давайте снова сделаем Михаила активным, чтобы он мог получать информационные сообщения:
Удаление оператора
Для удаления оператора используется процедура sp_delete_operator, которая выглядит следующим образом:
Здесь всего два параметра:
- @name – имя удаляемого оператора;
- @reassign_to_operator – не обязательный параметр, где можно указать оператора, которому должны быть назначены события, отслеживаемые удаляемым оператором.
Следующий пример удаляет оператора Михаил, а все события, которые он отслеживал, будет теперь отслеживать Андрей:
3.5.3. Добавление шага
Мы научились создавать и удалять работу, а также добавлять операторов, но все это пока лишено смысла, ведь создаваемая работа еще ничего не умеет делать. Чтобы наделить смыслом предыдущие несколько страниц данной книги, необходимо научиться создавать шаги работы. Для этого используется процедура sp_add_jobstep. В общем виде процедура выглядит следующим образом:
В первой строке указано, что процедуре необходимо указать или идентификатор или имя работы, которой нужно добавить новый шаг. Давайте рассмотрим параметры более подробно:
- @job_id – идентификатор работы, куда нужно добавить шаг;
- @job_name – имя работы, которой нужно добавить шаг;
- @step_id – автоматически увеличиваемое число, начиная с единицы. Если параметр не указан, то значение будет установлено автоматически (следующее за максимально существующим номером шага), т.е. шаг будет добавлен в конец цепочки шагов. Если у вас уже есть шаги с номерами 1, 2 и 3, и вы указали в параметре @step_id число 3, то новое значение будет вставлено на третью позицию, а шаг, который был на этом месте ранее, сместиться на четвертую. При этом, идентификаторы шагов будут перенумерованы автоматически;
- @step_name – имя шага;
- @subsystem – система команд, которые будут выполняться сервером на данном шаге. В этом параметре можно указать одно из следующих значений:
- ACTIVESCRIPTING – выполняться должен активный сценарий;
- CMDEXEC – на данном шаге будет выполняться команда ОС или внешняя программа;
- DISTRIBUTION – выполнение дистрибутора репликации;
- SNAPSHOT – выполнение агента репликации снятия снимка;
- LOGREADER – выполнение агента чтения журнала;
- MERGE – выполнение агента репликации смешивания (Merge);
- TSQL – шаг будет выполнять команду Transact-SQL (это значение по умолчанию).
- 1 – выход с сообщением об удачном завершении. Если работе установлен оператор, отслеживающий удачное завершение работы, то он получит соответствующее уведомление;
- 2 – выход с ошибкой. Иногда удачное выполнение команды является отрицательным результатом. Например, запрос может проверять наличие несвязанных строк. Если строки найдены, то по идее шаг должен вернуть положительный результат, но не связанные строки – это нарушение целостности. Поэтому можно указать, что при удачном выполнении сценария, необходимо завершить работу с ошибкой и проинформировать оператора, если он установлен;
- 3 – перейти к выполнению следующего оператора;
- 4 – перейти к выполнению шага, указанного в параметре @on_success_step_id.
- 0 – нет никаких дополнительных опций (это значение по умолчанию);
- 2 – результат выполнения должен добавляться в выходной файл, указанный в параметре @output_file_name;
- 4 – перезаписать выходной файл. Существующее содержимое будет уничтожено.
Давайте добавим в работу с именем ‘Тестовая работа 2’ два шага. На первом шаге будет удаляться таблица tbAndrey, а на втором, эта же таблица будет создаваться с помощью оператора SELECT INTO. Создание первого шага для решения данной задачи может выглядеть примерно следующим образом:
Данный шаг будет выполнять команду Transact-SQL, а значит, в параметре @subsystem указываем значение ‘TSQL’. В параметре @command указываем непосредственно SQL команду. Так как по умолчанию запрос будет выполняться в базе данных master, то в параметре @database_name явно указываем свою базу.
Основанная задача работы – создать таблицу tbAndrey и заполнить значениями, но для этого сначала старую таблицу нужно удалить. А что если старой таблицы нет (ее кто-то удалил или вообще ее небыло)? В этом случае все равно работа должна продолжать выполняться, поэтому в параметрах @on_success_action и @on_fail_action указываем значение 3, то есть переход на следующий шаг.
Теперь создадим второй шаг, на котором будет производиться создание таблицы:
В данном случае переход на следующий шаг не ожидается, поэтому завершаем работу с соответствующим кодом.
Когда вы пишете сценарий для работы, вы можете использовать некоторые вспомогательные конструкции, которые во время выполнения будут заменяться на определенные параметры. Во как сказал! Рассмотрим возможные конструкции, и на что они заменяются:
- [A-DBN] – заменяется именем базы данных;
- [A-SVR] – заменяется именем сервера;
- [A-ERR] – номер ошибки;
- [A-SEV] – критичность ошибки;
- [A-MSG] – заменяется текстовым сообщением;
- [DATE] – текущая дата;
- [JOBID] – идентификатор работы;
- [MACH] – имя компьютера;
- [MSSA] – имя сервиса SQLServerAgent;
- [SQLDIR] – директория, в которую установлен SQL сервер;
- [STEPCT] – сколько раз был запущена работа (количество попыток);
- [STEPID] – идентификатор шага;
- [TIME] – текущее время в формате HHMMSS;
- [STRTTM] — время в формате HHMMSS, когда работа была запущена;
- [STRTDT] – дата в формате YYYYMMDD когда работа была запущена.
Будьте внимательны, все конструкции должны заключаться в квадратные скобки, и все они чувствительны к регистру.
Давайте посмотрим, как использовать эти конструкции, а заодно увидим, как можно вставлять новые шаги. Следующим пример не добавляет новый шаг, а вставляет его на первую позицию (параметр @step_id равен 1):
В данном примере, в качестве команды в таблицу tbPeoples вставляется строка, в которой имени назначается имя компьютера (конструкция [MACH]), а в качестве фамилии указывается текущий номер шага (конструкция [STEPID]).
Обратите внимание, что в параметре @command, в SQL запросе в параметре VALUES вставляемые в таблицу значения должны быть в одинарных кавычках, а мы указали по две одинарных с каждой стороны. Почему? Дело в том, что вся команда INSERT должна быть в одинарных кавычках:
Если внутри команды используется одинарная кавычка, то сервер воспримет ее как конец команды, и он станет преждевременным. Например:
В данном случае, сервер поместит в параметр @command только строку:
Именно этот текст находиться между первыми двумя кавычками. Чтобы этого не произошло, внутри команды нужно продублировать все одинарные кавычки, что и происходит в примере выше.
Автоматически заменяемые во время выполнения конструкции действительно могут упростить разработку работ и иногда оказываются незаменимыми, особенно даты и время выполнения работы.
Давайте создадим еще один шаг, на котором будет выполняться системная команда, и при этом этот шаг мы вставим под номером 2:
Getting Started with SQL Server Agent — Part 1
I am a SQL Server Developer who recently migrated to a team with data integration production and monitoring functions. Everybody on the team uses SQL Server Agent, but I have practically no hands-on experience with it. Please give me a quick tutorial on creating a job, adding a schedule, and monitoring the operation of SQL Server Agent Jobs.
Solution
One especially fast way to ramp up to speed with SQL Server Agent is to use the Jobs and Jobs Activity Monitor icons under SQL Server Agent in Object Explorer within SQL Server Management Studio (SSMS). These icons open options for manually creating, configuring, controlling the operation of, and monitoring SQL Server Agent Jobs. While it is useful to program SQL Server Agent Jobs, it is not necessary to start deriving much value from it — especially during your initial introduction to it.
The tips covered here will focus on how to create and run SQL Server Agent Jobs. SQL Server Agent Jobs can be invoked on demand or on a schedule. You will initially learn about running a job on demand and later about how to assign a schedule to the job. The tip will also demonstrate how two SQL Server Agent Jobs can complement one another to satisfy a reporting requirement.
One of the most valuable manual features of SQL Server Agent is its Activity Monitor. You can use this feature to view the current status of a job as well as their past and prospective future performance on a SQL Server instance. This tip demonstrates selected menus from the Activity Monitor environment.
Creating your first two SQL Server Agent Jobs
To run a job on demand means the job has no schedule or is being run independent of the schedule. For example, you have to specifically invoke the job each time that you want it to run. One of the main advantages of SQL Server Agent is that it can schedule SQL Server Agent jobs. A job can be as simple as a T-SQL script. All the examples in this tip run T-SQL scripts. The first script creates a table in a database. The second script inserts a row into the table. By running the second job, we will be able to add a row to the table created in the first script.
To start your first SQL Server Agent job, open Object Explorer in SSMS and verify SQL Server Agent is running. The SQL Server Agent icon is available at the same level as the Databases icon. While Databases is the top icon underneath the Server icon in Object Explorer, SQL Server Agent is the last icon at the Databases level underneath the Server icon.
If the team to which you belong is regularly using SQL Server Agent, the feature will be on. However, if it is not on, you can right click the SQL Server Agent icon and choose Start. A green arrowhead next to the icon will indicate that it is on.
Right below the SQL Server Agent icon is the Jobs icon. You can use this icon to start a new job. Every job must have a name and at least one job step. All the jobs in this tip will have just one step. In the screen shot below, you see the SQL Server Agent icon and the Jobs folder below it.
Right click the Jobs folder and choose New Job to start a new job. This opens the New Job dialog. You can use this dialog to assign a name to the job. Additionally, you can add descriptive text about the job. See a completed example below of a New Job dialog with a name and description for a job.
Click the Steps page within the New Job dialog to present a dialog for adding a step to the job. Each step within a job designates what the step does. As with each job, every step within a job must have a name. Click the New button on the Steps page to assign a name to a step and indicate what the job step should do.
The following screen shot shows a completed Steps page. Notice that both the Step name and Command boxes have entries. The step name can reflect the role of the step. If you are using T-SQL to designate what the step should do, you can type or paste you code into the Command box. The code in the screen shot below assumes the prior creation of a database named for_SQL_Server_Agent_jobs. The code creates a fresh version of a table named RecordsNumericandDateTimeValues in the for_SQL_Server_Agent_jobs database.
The next two screen shots show key dialog boxes for creating a new job named Insert a value in a table. The table name is RecordsNumericandDatetimeValues in the for_SQL_Server_Agent_jobs database. The job has just one step that inserts 1 into the number_value column and the current datetime value into the date column of the table.
Run SQL Server Agent Jobs and Diagnose Performance
You can attempt to run a job by opening Job Activity Monitor with a View Job Activity menu selection, selecting the job, and invoking the Start Job at Step command from the context menu. The following screen shot shows the Job Activity Monitor just prior to the invoking of the Start Job at Step command for the Create a table job. The command will create a fresh copy of the RecordsNumericandDateTimeValues table in the for_SQL_Server_Agent_jobs database.
Next, we can invoke the Insert a value in a table job by selecting this job instead of the Create a table job. After successfully invoking both jobs, the RecordsNumericandDateTimeValues table will have one row in it that reflects the date and time when an insert was performed. The following screen shot shows a query and result set confirming the addition of one row to the RecordsNumericandDateTimeValues table.
It is possible for jobs in a production environment to fail for any of a variety of reasons. In the context of our example, the Insert a value in a table job can fail if the RecordsNumericandDateTimeValues table is dropped after the Create a table job is run and before the Insert a value in a table job is run. This can happen if some administrator or process inadvertently drops the table in the for_SQL_Server_Agent_jobs database. To show you how to diagnose a run-time failure, the RecordsNumericandDateTimeValues table was purposely dropped.
The following screen shot shows the outcome of trying to run the Insert a value in a table job when the the RecordsNumericandDateTimeValues table does not exist. Notice that an error is reported, but you are referred to the history log for additional details.
You can show the history log for a job step with two successive commands. First, select the job in Job Activity Monitor. Complete the first step by choosing the View history command from the context menu. The following screen shot shows the view just before invoking the View history command.
The second step involves selecting the job step with the failure in Log File Viewer. From this viewer, you can examine the message describing the error. In our example, the Message indicates the job failed because of an invalid object named RecordsNumericandDateTimeValues. Recall that the table with this name was manually dropped prior to invoking the Insert a value in a table job.
Adding a job with a schedule
Recall that one of the main advantages of SQL Server Agent is that it can run a job on a schedule. This section introduces you to the basics of adding a job that runs on a schedule.
Choose New Job from Jobs icon under SQL Server Agent. Assign a name to the job and optionally a description. The following screen shot shows the entry for a new job named Insert a value in a table with a schedule. A brief description is also assigned.
Next, select the Steps page. Click New towards the bottom of the page. Populate the Step name and Command boxes on the New Job Step page as indicated below. Then, click OK.
Next, select the Schedules page for the job. Then, click New.
- In the Name box type: Run daily every 5 minutes
- In the Schedule type drop-down box select Recurring
- In the Frequency settings,
- Choose Daily for the Occurs drop-down box and
- Enter 1 day(s) for Recurs every
- Choose the Occurs every radio button
- Enter a value of 5 and
- Select a value of minute(s) from the drop-down box
After completing the settings, click OK to enable the schedule for the job. Then, click OK to save all the settings for the job named Insert a value in a table with a schedule. These settings will create a job that inserts a value of 1 in the RecordsNumericandDateTimeValues table every 5 minutes throughout each day. The job runs every day of the week because of the settings in the Frequency settings of the schedule page. Because SQL Server Agent is a SQL Server feature, the execution of the schedule for a job depends on the SQL Server instance for a SQL Server Agent being operational.
Running a SQL Server Agent Job with a Schedule
The job created with the preceding steps cannot run unless there is a RecordsNumericandDateTimeValues table in the for_SQL_Server_Agent_jobs database. Because this table is currently deleted, you must invoke the Create a table job before launching the Insert a value in a table with a schedule job. You can invoke the Create a table job from Activity Monitor by selecting the job and choosing Start Job at Step.
Next, you can select the Insert a value in a table with a schedule job in Activity Monitor and invoke it in the same way. This will cause the job to run indefinitely unless a menu selection stops the job, some error condition is encountered, or the server suspends operation for any reason. Recall that the job runs every 5 minutes of every day. At some point in the future, you can validate the operation of the job. For example, you can select all the rows in the RecordsNumericandDateTimeValues table.
To demonstrate the operation of the job, it was invoked on April 9, 2017 at around 7:08 PM. The query and screen shot below with a result set for the first 14 executions of the job appear below.
- The first record inserted into the newly created RecordsNumericandDateTimeValues table was at 19:08 hours on April 9, 2017; hours are represented on a 24-hour per day basis.
- A second record was inserted just after 19:10 hours, the commencement of the first 5-minute interval within an hour after 19:08.
- About every 5 minutes to within milliseconds, an additional record is inserted into the table.
Creating a job to summarize the outcome from another job
While it may sometimes be helpful to see the results for each operation of a job, it is also valuable to obtain a more summarized view of how an application is working. The next example illustrates how to provide this kind of capability with a new table and a new job. Instead of viewing the outcomes from the Insert a value in a table with a schedule job for each 5-minute interval, we can count the entries in the RecordsNumericandDateTimeValues table for each day. For example, the following query counts the number of rows entered into the table on each day.
Assume that a requirement is issued for this kind of summary information. The requirement is for summary information being updated through midnight on each day. A SQL Server Agent job can automate the implementation of this capability through a scheduled job.
First, we need a new table into which to save the summary information. The following screen shot displays the sole job step for the Create a summary table job. As you can see, the job step’s name is Create a fresh summary table. The T-SQL in the command box performs these functions.
- Switch context from the default master database context to the for_SQL_Server_Agent_jobs database.
- Drop any previously existing version of the InsertsPerDay table in the database if one exists.
- Create a fresh version of the InsertsPerDay table with two columns: one named date and a second named Number of Inserts.
- Finally, the job step code specifies a primary clustered index on the date column. This is appropriate because the table is meant to have just one row per date value with the number of inserts for the date value on a row.
This Create a summary table job does not have a schedule. It is designed to operate on demand whenever there is the need to create a fresh copy of the InsertsPerDay table in the for_SQL_Server_Agent_jobs database.
The next screen shot shows the Freshly populate the InsertsPerDay table job step dialog from the Populate the InsertsPerDay table job. This job uses a different approach to reference a table in a job step than previously covered job steps. Instead of switching the context from the master database to the for_SQL_Server_Agent_jobs database, the code leaves the default database context to its default value of the master database. References to tables include a database name qualifier pointing at the for_SQL_Server_Agent_jobs database.
The job step has just two purposes.
- It initially truncates the InsertsPerDay table in the dbo schema of the for_SQL_Server_Agent_jobs database.
- Next, the job step uses an INSERT statement to populate the InsertsPerDay table with the result set from a query that counts the number of inserts per date value for all rows in the RecordsNumericandDateTimeValues table. These inserts are originally generated by the Insert a value in a table with a schedule job; see the Adding a job with a schedule section above to review this job.
In order for the Populate the InsertsPerDay table job to refresh the InsertsPerDay table at midnight on each day, you can specify a schedule like the one displayed in the screen shot below. The schedule name for this job is Daily at midnight. This schedule can be added to the Populate the InsertsPerDay table job in the same way that a schedule was added to the Insert a value in a table with a schedule job.
Notice that the job operates every one day just like the schedule for the Run daily every 5 minutes schedule for the job step in the Insert a value in a table with schedule job. However, one important difference is that this second schedule operates just once per day at midnight (12:00 AM).
Validating the Create a summary table job over three days
The Create a summary table job populates the InsertsPerDay table with the number of inserts on a daily basis into the RecordsNumericandDateTimeValues table. Recall that the Insert a value in a table with a schedule job populates the RecordsNumericandDateTimeValues table. A test run of these two jobs was conducted starting on 2017-04-09 19:08:21.340 and ending on 2017-04-12 03:30:00.990. Over this time span, there are three midnights (00:00:00.000) — one each on April 9, 10, and 11. An exogenous event caused the computer to halt operation after the last insert into the RecordsNumericandDateTimeValues table at 2017-04-12 03:30:00.990.
The following query returns the contents in the InsertsPerDay table for dates from 2017-04-09 through 2017-04-11. The following screen presents the result set from the query. The number of inserts on the tenth and eleventh of April cover 24-hour periods. Notice there are 288 inserts on each of these days. Within a 24-hour period, there are 288 5-minute intervals (12 per hour times 24 hours).
Another way of validating the results is to count independently the rows per day in the RecordsNumericandDateTimeValues table for each day from 2017-04-09 through 2017-04-11. The following three queries show the independent query for each day from 2017-04-09 through 2017-04-11. The screen shot below the query listings display the three corresponding result sets. Notice that the count for each day independently from the RecordsNumericandDateTimeValues table match exactly the values from the InsertsPerDay query.
Next Steps
- The SQL Server Agent tips covered here are very basic, but the tips are representative of the kinds of activities that beginning SQL Server Agent users are likely to invoke when creating and running single-step jobs. Furthermore, the tips include an introduction about how to examine and debug the operation of SQL Server Agent jobs.
- One critical element of a SQL Server Agent job is creating a schedule for when jobs run. The basics of assigning schedules to jobs are demonstrated in this tip.
- Two key jobs presented in this tip are the Insert a value in a table with a schedule job and the Populate the InsertsPerDay table job. These two jobs interact with each other in that the InsertsPerDay table job generates summary results on a daily basis for results from the Insert a value in a table with a schedule job. The tip closes with a validation of how the summary results from the InsertsPerDay table job are valid for inserts generated by the Insert a value in a table with a schedule job. Hopefully, you will find the demonstrations presented here helpful in implementing your own custom requirements.
About the author
Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade — especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.SQL Server Services
A service is a program that runs in the background that does not require any user interaction. Some examples of services are the drivers for your keyboard and mouse, your antivirus software, and the SQL Server Service.
The SQL Server Service is the executable process that IS the SQL Server Database Engine. The Database Engine can be the default instance which will be listed as: SQL Server (MSSQLSERVER) (limit one per computer), or the Database Engine can be one of many named instances listed as: SQL Server (instance_name). The SQL Server Service is not dependent on any other services to run. SQL Services can be configured to run as a domain user, local user, managed service accounts, virtual accounts, or built-in system account. For instances that do not require access to external resources, Microsoft recommends configuring SQL Server Service and SQL Server Agent to use the default virtual account, otherwise, it is recommended to use a Managed Service Account with the least privileges required. However, since only one instance of SQL Server Browser can run per server, it runs under a Local Service account by default. For more information about account permission and best practices, please see the following article: Configure Windows Service Accounts and Permissions.
Example of SQL Server Configuration Manager showing the installed SQL Services and differentiating between the default SQL instance MSSQLSERVER and a named instance DEVELOPMENT
The SQL Agent Service is responsible for running scheduled tasks and jobs. By default, SQL Agent Service is set to start manually. However, since it is often relied upon by scheduled maintenance, backup, and monitoring tasks, it is recommended that this service is set to start automatically. The SQL Agent Service is dependant on the SQL Server Service and therefore cannot run if the SQL Server Service is stopped. If the SQL Server Service is stopped manually, SQL Agent Service will not automatically start when SQL Server Service is manually started. In this specific scenario, SQL Agent Service will also need to be started manually.
SQL Browser Service listens to incoming client requests for SQL resources and provides connection information to the default and any additional named instances that are running on a particular machine. The SQL Browser Service acts like a DNS Server for SQL. It allows incoming client connections to request information on UDP port 1434 and then the Browser Service provides the specific TCP/IP port or named pipe information back to the client. Because the SQL Browser Service resolves issues that are caused by a variety of scenarios like multiple network cards, statically assigning a port to a SQL Server only to have a different service request that port first, or having dynamically assigned ports, for customized port configurations or servers running multiple instances, I highly recommend keeping this service enabled and set to start automatically.
Unlike the SQL Server Service which will have services running for each instance, only one SQL Browser Service is required to provide the necessary connection information for all of the instances on a machine. However, the SQL Browser Service is only able to provide information about instances running on its particular machine. It can not provide information about other SQL Servers that are running on different machines within the same network.
Three different ways to change the status of SQL Services
Below are examples of how to Start, Pause, Stop, and Restart the SQL Services using SQL Server Configuration Manager, the Command Prompt, and SQL Server Management Studio. While these tasks can be completed with SQL PowerShell and T-SQL statements, I chose the prior three methods because I felt they were easier to use when it came to applying these commands to named instances.
(Warning: While it is possible to start and stop the services using the services.msc, it is against best practices and may lead to database corruption.)
Local server administrators group membership is required by default to Start, Pause, Stop, or Restart services using SQL Server Configuration Manager, SQL Server Management Studio, or the Command Prompt using net commands. In situations where admin access to the OS has been lost, or granting server administrator privileges is not preferred, please consult you Systems Administrator and consider the alternatives presented in the following article: How to Start or Stop SQL Services without OS Admin Rights.
Using SQL Server Configuration Manager
Best Practice: Use the SQL Server Configuration Manager when making any changes to the SQL Services. It ensures that all of the changes made to the SQL Services get propagated to all of the necessary registry entries and applies any necessary permissions when changing the account the service is running under. SQL Server Configuration Manager also provides a validation check if changes are made to the service account used to start SQL Server Service. This prevents an invalid account being assigned to the SQL Server Service which will block the service from starting.
To use the SQL Server Configuration Manager, select the appropriate file for your SQL Server version and Run as Administrator:
SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
Example of SQL Server Configuration Manager with a default instance (MSSQLSERVER) and a named instance (Development).
Using the Command Prompt with net Commands
Caution: While it is possible to change the service’s status through the command prompt, it is best practices to use the SQL Server Configuration Manager.
Run the Command Prompt as the local administrator
For Named instances
Use the same Command Prompt commands, but replace MSSQLSERVER with the instance name (i.e. net start “SQL Server (Development)” ).
Using SQL Server Management Studio
Example of SQL Server Management Studio connected to the default instance and the “Development” named instance.
There are some limitations to using SSMS for managing the state of the SQL Services, so again I highly recommend using SQL Server Configuration Manager when making changes.
Starting
SQL Server Configuration Manager
- Right-click the instance you want to start and select “Start”.
- Note: If you manually start the SQL Server service, you will need to independently start the SQL Server Agent. Hint: Since SQL Server Agent is dependent on SQL Server Service, starting SQL Server Agent will start both services.
Command Prompt with net Commands
- To start the SQL Server Service enter the following command :
- net start “SQL Server (MSSQLSERVER)”
- To start the SQL Agent Service enter the following command:
- net start “SQL Server Agent (MSSQLSERVER)”
SQL Server Management Studio
NOTE: The only way to start a SQL Server instance within SSMS is if SSMS was connected to the instance prior to it being stopped.- Right-click the instance you want to start and select “Start”
- Click yes on the pop-up message to confirm that you want to Start the SQL Server Service
- After the SQL Server Service is started, right-click the SQL Server Agent and select “Start”
- Click yes on the pop-up message to confirm that you want to Start the SQL Server Agent Service.
Pausing
A special note about Pausing the SQL Server Service
Pausing the SQL Server Service allows current connections to stay active, but it prevents new connections. This is beneficial when you want connected users to be able to finish their tasks prior to stopping the SQL Server Service.
SQL Server Configuration Manager- Right-click the instance you want to pause and select “Pause”.
Command Prompt with net Commands
- net pause “SQL Server (MSSQLSERVER)”
SQL Server Management Studio
- Right-click on the instance and select “Pause”.
- Click yes on the pop-up message to confirm that you want to Pause the SQL Server Service.
Resuming
SQL Server Configuration Manager
- Right-click on the instance and select “Resume”.
Command Prompt with net Commands
- net continue “SQL Server (MSSQLSERVER)”
SQL Server Management Studio
- To Resume SQL Server Service, right-click the instance and select “Resume”.
Stopping
SQL Server Configuration Manager
- Right-click the instance you want to stop and select “Stop”.
- Note: Stopping the SQL Server service will also stop the corresponding SQL Server Agent.
Command Prompt with net Commands
- net stop “SQL Server (MSSQLSERVER)”
- You will be notified that SQL Server Agent will also be stopped and prompted to continue. To continue enter: y
- To stop just the SQL Agent Service enter the following command:
- net stop “SQL Server Agent (MSSQLSERVER)”
SQL Server Management Studio
- Right-click on the instance and select “Stop”.
- Click yes on the pop-up message to confirm that you want to Stop the SQL Server Service.
- Click yes on the pop-up message to confirm that you want to stop the SQL Server Agent Service.
- Note: SQL Server Agent Service can be stopped independently from SQL Server Service by right-clicking SQL Server Agent and selecting “Stop”.
- Click yes on the pop-up message to confirm that you want to stop the SQL Server Agent Service.
Restarting
SQL Server Configuration Manager
- Right-click the instance you want to restart and select “Restart”.
- Restarting the SQL Server service will also restart the SQL Server Agent.
Command Prompt with net Commands
(Stopping SQL Server Service using the Command Prompt will stop the SQL Agent Service. For the purposes of restarting SQL, both the SQL Server Service and SQL Agent Service will have to be individually started.)
For the Default Instance- net stop mssqlserver – enter ‘y’ to acknowledge that SQL Agent Service will be shut down.
- net start sqlserveragent (This will start both the SQL Server Service and the SQL Agent Service.)
- (To start just the SQL Server Service use: net start mssqlserver)
For Named Instances
- net stop MSSQL$Named_Instance_Here
- Net start SQLAgent$Named_Instance_Here
- (To start just the SQL Server Service use: net start MSSQL$Named_Instance_Here)
SQL Server Management Studio
- Right-click on the instance and select “Restart”.
- Click yes on the pop-up message to confirm that you want to restart the SQL Server Agent Service.
- Click Yes to acknowledge that SQL Server Agent Service will be restarted as well.
Submit a Comment Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.