Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Scheduling SQL Server Tasks on Linux


By:   |   Read Comments   |   Related Tips: More > SQL Server on Linux

Problem

Your starting to use Linux to run SQL Server and you need to automate some tasks, but you don't know how to do this on Linux. In this tip I will introduce you to the Linux Cron utility.

Solution

Task scheduling is a key factor for the enterprise. Whether the company is a small, medium or a large enterprise, there are tasks that can't be done during working hours. Even when the company has employees working to provide 24/7 service, it could be the case that there are so many tasks to execute it makes it impossible for employees to be able to run those tasks manually.

Every operating system that I am aware of provides a mechanism to schedule tasks. On Windows it is Task Scheduler, on MacOS it is Launchd and on Linux, UNIX and Solaris it is called Cron.

Cron and Crontab

On Linux and its related OSes (I mean POSIX compatible OSes), Cron is a task scheduler that runs as a daemon in the background. For those of you who don't know, on Linux services are called daemons. Cron gets its name for the Greek God of Time Cronos.

When we are starting out on Linux we may be confused about if the task scheduler is named cron or crontab. This is because when people want to schedule a job they refer to crontab instead of cron. Crontab is short for "Cron Table". It is a file stored in /var/spool/cron/crontabs directory on Ubuntu distributions (other distros may have different locations, like /var/spool/cron/ on RedHat). Did you notice the "s" at the end of the path? That's right, there are more than one crontab files. There could be as many files as users on the Linux system and each file will have the name of the owner/user. Of course, if a specific user has no scheduled tasks there won't be any file with that user name.

Also, there is another crontab file located in /etc/crontab. This is the system-wide crontab file. Most of the times I will be referring to the "users crontab file", so when I refer to the "system-wide crontab file" I will say it explicitly.

The cron daemon is started automatically from /etc/init.d/cron script by entering multiuser runlevels (2, 3, 4 and 5).

Crontab

The crontab file cannot be modified directly; with the exception of the system-wide crontab file that must be written directly by a user with root privileges. This is a safety measure of Linux to avoid syntax errors in crontab files. Remember in the previous paragraphs I told you that crontab is a file? Well, at this point things start to get complicated. There is also a program named crontab located at /usr/bin/crontab. This program is not a service like cron, it is invoked by the user. What this program does is acts as a wrapper to a text editor and also it is a parser that parses the contents of the crontab file for inconsistencies. I made a flow chart to explain this. Take a look.

This is how crontab script works.

Crontab has its own parameters that I will describe in the next table:

Parameter Description
-u Specifies the name of the user whose crontab file is to be used.
-i Prompts the user for confirmation when removing the crontab file.
-e Edits the crontab file.
-l Displays the crontab file.
-r Deletes the current crontab file.

If you edit the crontab file for the first time you will be asked for a text editor as shown in the next image.

You have to select your default editor in order to edit Crontab file.

On the previous image you can see the crontab program on its first execution asks you if you want to use nano, vim or ed as the text editor. Also, the message says that if we want to change the editor later we can run the select-editor command.

In my case I choose nano as the default editor. If you make the same selection you will see the default crontab file as shown on the next image.

Default crontab file on nano text editor.

Crontab File Format

If you were paying attention you know that the crontab program works as a parser for the crontab file. This implies that the crontab file has a specific format (otherwise there will no need for a parser).

A Crontab file has the following format:

Minute Hour DayOfMonth Month DayOfWeek User Command
Parameter Description
Minute It is the minute of a given hour on which the command will be executed. Of course, this value must be in the range 0 to 59.
Hour The hour on which the command will be executed on a 23 hour format. Values must be in the 0 to 23 range when 0 means midnight.
DayOfMonth  Day of the month on which you want to run the command.
Month  Month on which you want the command to execute. It could be written by using the month number (i.e. 1 to 12) or by the month first three letters (in English of course).
DayOfWeek  Day of week on which the command will be executed. It is a number in the range (0-7) where 0 and 7 represents Sunday. Also you can use the first three letters of the Day name, in English of course (sun, mon, tue, wed, thu, fri, sat).
User  The user context on which the command will be executed. This field is only present on System-Wide Crontab file.
Command The command that will be executed. It could be a script or a program.

In the next example, I am scheduling the script /home/daniel/script.sh to run every day at 05:00 AM.

0 5 * * * /home/daniel/script.sh

You can see I am using a wildcard; which is the asterisk (*). If you put an asterisk on any of the first five fields (i.e. those who are time dependent) it means all possible values. For example, if you put an asterisk on the hour field it means that the command will be executed each hour.

In the next example, I am scheduling the script /home/daniel/script.sh to run every day at every hour.

0 * * * * /home/daniel/script.sh

If you want to specify different values for a specific field you can separate each value with a comma (,). In the next example, I am scheduling the script to run at midnight and noon (0,12).

0 0,12 * * * /home/daniel/script.sh

Now let's make the script run every six hours (0,6,12,18).

0 0,6,12,18 * * * /home/daniel/script.sh

Also, you can define ranges with a hypen(-). For example, suppose you want to run the script every hour between 09:00 to 18:00 (9-18) from Monday to Friday (1-5). You can do so with this command:

0 9-18 * * 1-5 /home/daniel/script.sh

If you couldn't specify the range, you would have to do as follows, which can be a bit tedious.

0 9,10,11,12,13,14,15,16,17,18 * * * /home/daniel/script.sh

Also, crontab allows us to define steps by using the "/" character followed by the number of steps. For example, "*/2" skips every two steps. In the next example, I will schedule the script to run every 15 minutes.

*/15 * * * * /home/daniel/script.sh

Now I will schedule the script to run at midnight every other day.

 0 0 */2 * * /home/daniel/script.sh

Reserved Words

There are also reserved words that you can use on the crontab file that aid in writing common events in a friendlier way.

Reserved Word Description Equivalent
@reboot Runs each time the system starts. N/A
@yearly Runs once a year at January 1st midnight. 0 0 1 1 *
@annually Same as @yearly 0 0 1 1 *
@monthly Runs the first day of the month at midnight. 0 0 1 * *
@weekly Runs once a week at Sunday midnight. 0 0 * * 0
@daily Runs once a day at midnight. 0 0 * * *
@midnight Same as @daily 0 0 * * *
@hourly Every hour 0 * * * *

In the next example I will schedule the script to run at system start.

@reboot /home/daniel/script.sh
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools