Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Creating a regularly scheduled job

Creating a regularly scheduled job

  • Comments 5

The goal of this post is to show how you can create a schedule and attach an existing job to that schedule.

Create a schedule on an existing job (or create a job as described in a number of previous posts).

1) Open SQL Server Manament Studio (SSMS), connect to SQL Server, and right click on the Sql Server Agent \ Jobs \ Your Job.

Select Properties and click.1

2) Click on the Schedules Page on the left, then click New… under Schedule list.

If the job already has a schedule, you can edit it or remove it.

2

 

3) Enter a name for the schedule, then select the type of schedule you want.

Here is some information:

Schedule Type: the option are straightforward. For “Start whenever the CPUs become idle” you need to set up an idle CPU condition (see http://msdn.microsoft.com/en-us/library/ms189065.aspx)

Enabled: here you can disable a schedule without deleting it.

You can select what type of occurrence, the days of the week and/or how many days/weeks/months to skip between executions.

For daily schedules, you can specify how many seconds/minutes or hours between executions.

Note: By default, Sql Agent will not execute a job more often than four times a minute (every 15 seconds). In general, the scheduler cannot be used for “real time” events. The job start can be delayed depending on the system load.

3

 

Check that the next job execution will be triggered at the right time.

After creating or updating a schedule, you can check the next execution date and time.

Preconditions: Sql Agent has to be running for the next execution date/time to be calculated.

In SSMS, expand the Sql Server Agent node under Object Explorer, then double-click on Job Activity Monitor. Look for your job name and check the column “Next Run”.

4

Check the job was executed at the right time after the scheduled time has passed.

You can check the job history at any time to confirm that the schedule was triggered correctly and the job succeeded.

Just right click on the job and select “View History”.

5 

This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • Is the Job History cleared whenever the log is truncated? "BACKUP * WITH ... TRUNCATE"  Where I work they've decided to truncate logs instead of backing them up.  I have noticed that the agent job I setup here to run over the weekend doesn't show any job history.  It does however indicate that it was run in the "Last Executed" field.

  • hi,thanks for your Instructions

    but when i want to backing up from a DB every day automaticly,The second will overwrite the previous backup.

    i want to have one Separated back up every day

    please help me

  • Hi sahar. This will help you

    DECLARE @name VARCHAR(50) -- database name  

    DECLARE @path VARCHAR(256) -- path for backup files  

    DECLARE @fileName VARCHAR(256) -- filename for backup  

    DECLARE @fileDate VARCHAR(20) -- used for file name

    -- specify database backup directory

    SET @path = 'G:\DATA BACKUP\'  

    -- specify filename format

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

    SET @name='test';

    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  

    BACKUP DATABASE @name TO DISK = @fileName  

  • hi,

    What to do if there is no SQL server agent folder at all?  The last one is Management. :(

    It is an SQL Server 2008 R2 and management studio: 10.50.4000.0

    thanks

  • Can we call a stored procedure daily at a fixed time for a fixed duration throuh scheduler. if it so, then please tell me the steps.

Page 1 of 1 (5 items)