Analysis of Schedules SQL SERVER operation

SQL SERVER operation plan (Schedules), if you do not carefully studied with or without the application of some complex plan (Schedules), so you think SQL SERVER operation plan (Schedules) is very good, also do not have what problem, but I want to tell you a &ldquo & rdquo; the cruel truth, compared to Linux; the Crontab SQL SERVER operating system, the scheme is in fact a two disabled, many functions to implement it is to let a person headache!

If you think very understanding job allocation (not most probably it did not actually happen, practice shows that will let you be startled at)? Then have a look first few questions:

1: If you want to configure the job in each hour twentieth minutes to perform, such as 1:20, 2:20, 3:20..... Execution, how you configure?

2: In the program (Schedules) which, if executed interval for &ldquo hours, then; ” the value must be an integer? For 2.5 hours?

3: In the program (Schedules) which, if I want to set up the execution interval is 90 minutes, so you can do it?

4: If I want to plan (Schedules), to perform an operation, how to implement? Implementation of the plan, would not affect the operation of Schedules?

5: Modification to the system, whether it will affect the operation of Schedule?


1: If you want to configure the job in each hour twentieth minutes to perform, such as 1:20, 2:20, 3:20..... Execution, how you configure?

For the problem of 1 such similar problems, if you encountered and solved this problem, it is actually very simple, you only need to set the start time of 0:20 can be, otherwise unattainable. As for the SQL SERVER why so boring settings, should be performed interval has a reference time point (the first implementation of the time), you set the start time for 0:20, said that this time point as the first implementation of the reference point.


clip_image002


2: In the program (Schedules) which, if executed interval for &ldquo hours, then; ” the value must be an integer? For 2.5 hours?

On 2, the answer is execution interval is not a decimal integer, only, only 1, 2, 3.... If you set the value to a decimal, then when you click the OK button, it will automatically four to five homes in into an integer, for example, you enter 1.3, you click OK, then open, you'll find out its value is 1, if it is 1.6, it will become 2, don't believe me, you can try. For the second, minute is. Why, the answer lies in the system table msdb.dbo.sysschedules, don't understand can look at last.


3: In the program (Schedules) which, if I want to set up the execution interval is 90 minutes, so you can do it?

If no practice of people, will take an oath devoutly say, it certainly can. The answer is to set the execution interval must not exceed 61, otherwise when you click OK, the system automatically will be set to 60, as for the reason, I do not want to understand, Microsoft this limit, feel really boring. If there is a need for 90 minutes once, how does that do? Tangle.

Methods 1: The J split into two as like as two peas job J1 and J2, then set the Schedules execution time is 3 hours, two operation staggered execution, just to meet business needs. But think of all that trouble.!


Methods 2: Job Schedule execution time interval implementations of 90 minutes is realized through the following ways: it is the Schedule time is executed once for 30 minutes, then the code inside the according to the law, achieve 90 minutes before the implementation of the real business logic SQL code.

2.1 From the 0:00 (server time), operating frequency of Schedule for 30 minutes at a time, in fact, only need to perform the following time points (the actual execution time of 90 minutes), can be divided into two kinds according to the law:

           The whole point of execution (0:00, 1:00, 2:00, 3:00…..) only when Hour%3 = 0 when the execution of the stored procedure.

           Non integer point execution (0:30, 1:30, 2:30, … …...) only when Hour% 3= 1 when the execution of the stored procedure.

2.2 From the 0:30 (server time), operating frequency of Schedule for 30 minutes at a time, in fact, only need to perform the following time points, can be divided into two kinds according to the law.

           The whole point of execution (0:00, 1:00, 2:00, 3:00…..) only when Hour%3 = 2 when the execution of the stored procedure.

           Non integer point execution (0:30, 1:30, 2:30, … …....) only when Hour% 3= 0 when the execution of the stored procedure.


clip_image004

DECLARE @Hours   INT;

DECLARE @Minute    INT;

SELECT @Hours =CAST(SUBSTRING(CONVERT(VARCHAR(16), GETDATE(), 120),11,3) AS INT);

SELECT @Minute=CAST(SUBSTRING(CONVERT(VARCHAR(16), GETDATE(), 120),15,2) AS INT);

IF @Minute = 30 AND @Hours%3 = 0

BEGIN 

       INSERT INTO BI_JOB_LOG   --The temporary recording execution time

       SELECT GETDATE();

        --Execute the business logic code

        ...........................

END;

IF @Minute = 0 AND  @Hours%3=2

BEGIN

       INSERT INTO BI_JOB_LOG

       SELECT GETDATE();

        --Execute the business logic code

        ...........................

END;


4: If I want to plan (Schedules), to perform an operation, how to implement? Implementation of the plan, would not affect the operation of Schedules?

As shown below, I set up a test test, every 13 minutes to perform a, 21:57, 21:58 are manually performed this operation two times, but did not affect the scheduling plan. In 22:06 minutes or execution.

clip_image006

To view the job the next time, you can view the SELECT * FROM msdb.dbo.sysjobschedules.


5: Modification to the system, whether it will affect the operation of Schedule?

The answer is affected, either the reconstruction of the scheduling plan or disable scheduling, to enable the scheduling to solve. You can test your interested, here don't do too much introduction.

SQL SERVER database operations Schedule information is generally located in the msdb.dbo.sysschedules table, as shown below

Contains information about the SQL Server agent job planning information. This table is stored in the msdb The database. In fact, the data type of the parameter, you'll see the answer to the second question. Ha-ha

The column name

Data type

Explain

schedule_id

int

The SQL Server agent job plan ID.

schedule_uid

uniqueidentifier

Unique identifier of the job schedule. This value is used to identify the distributed operation plan.

originating_server_id

int

As the operation plan source primary server ID.

name

sysname (nvarchar(128))

User defined name operation plan. The name must be unique within the operation.

owner_sid

varbinary(85)

Has the operation plan of the user or group Microsoft Windows security_identifier.

enabled

int

Schedule status:

0 = Not enabled.

1 = Enable.

If the plan is not enabled, you will not run any operation in the plan.

freq_type

int

The frequency of operation plan.

1 = Only once

4 = Every day

8 = Weekly

16 = Monthly

32 = Monthly, and freq_interval Of

64 = Running on the SQL Server agent service start time

128 = Run when the computer is idle

freq_interval

int

Execution interval operation. The value depends on the freq_type The value of the. The default value is 0, Don't use freq_interval.

freq_type The value of the

The effect on freq_interval

1Once.

Do not use freq_interval (0)

4(day)

Each freq_interval Day

8(weekly)

freq_interval For one or more of the following values:

1 = Sunday

2 = Monday

4 = Tuesday

8 = Wednesday

16 = Thursday

32 = Friday

64 = Saturday

16(monthly)

Monthly freq_interval Day

32(“ &rdquo per month; option related)

freq_interval As one of the following values:

1 = Sunday

2 = Monday

3 = Tuesday

4 = Wednesday

5 = Thursday

6 = Friday

7 = Saturday

8 = Day

9 = Working day

10 = Rest day

64 (at the beginning of the SQL Server agent service startup)

Do not use freq_interval (0)

128(run when the computer is idle)

Do not use freq_interval (0)

freq_subday_type

int

freq_subday_interval Company. Can be one of the following values:

Value

Description (unit)

1

At the appointed time

2

Seconds

4

Minute

8

Hours

freq_subday_interval

int

Occur between each execution of the job. freq_subday_type The number of cycles.

freq_relative_interval

int

If freq_interval For the 32(“ &rdquo per month; options), for the month freq_interval Time of occurrence. Can be one of the following values:

0 = Do not use freq_relative_interval

1 = For the first time

2 = Second times

4 = Third times

8 = Fourth times

16 = The last time

freq_recurrence_

factor

int

Between the scheduled execution of a job number of weeks or months. Only when the freq_type For the 8, 16 Or 32 When use, freq_recurrence_factor. If this column contains 0, Do not use freq_recurrence_factor.

active_start_date

int

Job execution can begin date. The date format YYYYMMDD. NULL said the date.

active_end_date

int

Job execution can stop date. The date format YYYYMMDD.

active_start_time

int

In the active_start_date And active_end_date Any date between the beginning of the job execution time. The time format is HHMMSS, the 24 hour.

active_end_time

int

In the active_start_date And active_end_date Any date between the end job execution time. The time format is HHMMSS, the 24 hour.

date_created

datetime

Date and time the creation of plans.

date_modified

datetime

The schedule was last modified date and time.

version_number

int

The current version number plan. For example, if the plan has been revised 10 times, then version_number 10.

Posted by Julia at November 23, 2013 - 7:36 PM