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.
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.
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.
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
- New on Linux? This is where you must begin: Getting Started with SQL Server on Linux.
- After reading the previous tip I suggest that you read 7 Things Every SQL Server DBA Should Know About Linux.
- On the following tip you will find the most used commands for SQL Server DBA’s: Top 10 Linux Commands for SQL Server DBAs.
- Also you want to learn other Linux administration basics you can read my previous tips on which I cover how to check for CPU Usage, Disk I/O and Disk Space.
- For more information regarding SQL Server and Linux check the SQL Server on Linux Tips Category.
