Job в sql что это

Job в sql что это

SQL Server Jobs

SQL Server. Активные задания

Сегодня я бы хотел рассмотреть вопрос заданий, которые сейчас выполняются на сервере. Есть множество способов получения нужной нам информации и мы сегодня рассмотрим некоторые из них:

1. Самый простой способ, но не самый полезный это просто использовать процедуру msdb.dbo.sp_help_job:

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

2. Используя ту же процедуру мы можем загнать данные в табличную форму, с которой можно будет работать, но и тут не обойдёмся без минусов, нам придётся использовать функцию OPENROWSET:

Если вы испытываете сложности с данной функцией, то необходимо указать верные параметры. В моём случае я использую встроенную систему безопасности без указания пароля. Возможно потребуется изменить параметры примерно на следующие ‘Server=SERVER_NAME\INSTANCE_NAME;uid=user;pwd=111111′ вместо Server=(local);Trusted_Connection=yes;’

How to schedule a MSSQL Server Job

Download SQL Server Management Studio from the link below:

Download SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server…

It is compatible to the following OS: Windows 10, Windows 8, Windows 8.1, Windows 7 (SP1), Windows Server 2016, Windows Server 2012 (64-bit), Windows Server 2012 R2 (64-bit), Windows Server 2008 R2 (64-bit). As you can see this software is only for Windows users there are other software you can use like SQLPro to remote to SQL Server from Mac.

You can access your current SQL Server by installing this free software and connect to your from multiple computers based on your authentication.

Add a new Job

SQL Server jobs becomes very useful when we have a series of steps that we want to run after each other or we want to schedule them to run in a frequent basis. SQL Server Jobs are easy to setup and maintain. In the following step, I will explain how to create a new job.

  1. Connect to the server from SQL Server

2. After you are connected, from the left bar, find and expand SQL Server Agent/ Jobs: You can find list of all the scheduled jobs.

3. Right click on Jobs and select New Job

4. A new window will be open that you can fill the name and category of the job, there is also a description.

Job categories are helpful for organizing your jobs so you can easily filter and group them. For example, categorizing all your database backup jobs in the Database Maintenance category is easy to audit your jobs in view the backup history. You can also create your own job categories.

I recommend always add a description of the job running for yourself in the future and other who are looking at this job.

5. From the right menu, select Steps. In a job you can add multiple steps and they will run right after each other.

6. When you click on a New button, another window will open that you can type your Step name, Step Type and Command. Select a database, and select T-SQL as type. Enter your SQL command in the text area and press OK.

You can read more about different step types here.

You can add as many steps as you like to a single job and sort them, or you can set a job to start from a specific step, therefore it will skip prior steps if there is any.

Schedule a SQL Server Job

From the left bar of the job, click on Schedules.

Edit and New Windows are very similar so you can edit a schedule the same way as you are creating a new one.

Add a name for the schedule, make sure the enable checkbox is checked and pick a type from the drop down and select schedule start date and occurrence frequency. Then, press Ok.

Configure SQL Jobs in SQL Server using T-SQL

In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let’s discuss it one by one.

Introduction :
SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production servers during the non-Business hours only. So, we create a SQL Server job of running index maintenance and schedule it for “off” hours. When we install SQL Server, the SQL Server Agent is disabled. So at first we will enable it and start it manually. Then, we will configure the SQL Server job using SQL Server Management Studio and the system stored procedures of the MSDB database. So, this article explains how to create SQL Server Job using the system stored procedures of the MSDB database.

System stored procedures of the MSDB database :
SQL Server uses the following ones are as follows.

Query-1 : sp_add_job :
The procedure is for creating a new job. If it is successful, it returns @job_id. The following arguments are applicable as follows.

  • @job_name –
    It is a unique job name.
  • @enabled –
    Job is enabled or disabled. Once a job is created, you can set the parameter’s value as 1 to enable the job.
  • @notify_level_eventlog –
    This parameter is used for writing the status of the SQL Job in Windows event viewer.
  • @notify_level_email –
    It will send the mail on the basis of job outcome. The valid values of the parameter are the same as the @notify_level_eventlog argument values.
  • @notify_level_page –
    This parameter serves to send the pager notification of the SQL Job outcome. The valid values of the parameters are the same as the @notiify_level_eventlog argument values.
  • @delete_level –
    This parameter serves to delete the job after completion. In this case, the value of the parameter should be
  • Note –
    The default value is 0; then, it won’t delete the job after completion.
  • @category_level –
    This parameter indicates the job category values. The default value is Null.
  • @owner_login_name –
    The value is the domain name or the job owner’s SQL Login name.

Query-2: Sp_add_jobserver :
For SQL job execution, it specifies the target server. It accepts the following arguments as follows.

  • @job_id –
    In SQL job, it identifies the unique values, and it is a UNIQUEIDENTIFIER. Its default value is NULL.
  • @job_name –
    In SQL job, this parameter specifies the name of job.
  • @server_name –
    It is the name of the server where you want to run the SQL Job. The default argument value can be the local server (LOCAL) or the targeted server hostname.

Query-3: sp_add_jobstep :
This stored procedure works for adding the job step in SQL Job. The procedures use the following arguments as follows.

  • @job_name –
    The name of the job in which you are adding the step. It is an SYSNAME with NULL as the default value.
  • @step_name –
    The name of the step. It is an SYSYNAME with NULL as default value.
  • @step_id –
    The sequential ID of the Job step. It is an incremental number without a gap. It is an INT value, and the default value is NULL.
  • @cmdexec_success_code –
    This value is returned by the CmdExec subsystem. It indicates whether the command execution was successful. The code is int value with 0 as a default value.
  • @on_success_action –
    This value indicates the action that should be performed after the job step completes successfully. The values can be any of the following :
  • @on_fail_action –
    Specify what action to be performed if the job steps fail. It is an INT value, and the default value is NULL.
  • @retry_attempt –
    Specify the number of retry attempts after the job step failure. It is an INT value, and the default value is NULL.
  • @retry_interval –
    Set the time interval between two SQL job step failure attempts. It is an INT value, and the default value is NULL.
  • @Subsystem –
    Specify the name of the subsystem used by SQL Server Agent to execute the command. The valid values are the following:
  • @command –
    Specify the command that the SQL Server Agent Service should execute through the subsystem. The data type is varchar(max), and the default value is NULL.
  • @Database_name –
    Specify the name of the database where you want to run the command. This parameter is useful when you are running a T-SQL script using SQL Server Agent.

Query-4:Sp_add_jobschedule :
The stored procedures serve to create the SQL Job schedule. This procedure uses the following arguments as follows.

Ссылка на основную публикацию