Scheduling SQL Server Tasks on Linux Part 2: Advanced Cron Topics

By:   |   Comments   |   Related: > SQL Server on Linux


In a previous tip we looked at how to use Cron to schedule tasks for SQL Server on Linux. In this tip we take a deeper look to help answer these questions: Is there is any security configuration that I can use? Is there a way to view a log of cron work? How I can check if a job was successful or failed? In this tip I will answer those questions.


In my previous tip in this series I introduced you to the cron daemon and its configuration file, crontab. I omitted many items in order to make the introduction simple. My intentions with that tip were mostly to introduce you to the format of crontab file. Now I will cover more advanced topics.

Advanced Cron Topics

It is not enough to just know the crontab file format to use cron. Think about it. It is pointless to schedule a job if you won’t be able to know whether it was successful or failed.

Here is a list of topics I will cover in this tip:

  • Crontab Variables
  • Crontab Logging
  • Crontab Security
  • Crontab Shortcuts

Let’s start.

Crontab Variables

You can define and use variables in a crontab file. There are predefined variables where the name has a specific use. Take a look at the next table. Some of those variables have a default value which can be overridden by redefining it. Also, you can assign an empty string if you want to blank out the contents of a variable (i.e. VARIABLE="").

Variable Description Example Default Value
SHELL The shell used to execute each command in the crontab file. SHELL=/bin/bash /bin/sh
PATH Indicates the path to the directories in which cron will look for the command to execute. This path is different from the global path of the system or the user. PATH= /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin /usr/bin:/bin
MAILTO The addresses (separated with a comma) that will receive by mail the log of failed tasks. When this variable is declared as empty (i.e. MAILTO="") no mail is delivered. [email protected] Mail is sent to the owner of the crontab.
HOME This is the root directory for cron. HOME=/ Its value is taken from the /etc/passwd file for the user.
LOGNAME This is the user name that the job is running from. Its value cannot be changed. N/A N/A

Something important to note is that cron doesn’t perform variable substitution. In layman’s terms, you can’t assign a variable the sum or composition of other variables. Let’s consider the following example:


On the previous example the user who wrote the crontab lines wants the PATH variable to contain the directory specified in the HOME variable and additionally /bin and /usr/bin directories. But since there is no variable substitution in cron, the PATH will take "HOME:/bin/:/usr/bin" as a literal. The proper way to write the previous lines is as follows:


In the next image you will see a crontab file with SHELL and PATH variables declared.

This system-wide crontab file have the SHELL and PATH variables declared.

The next code fragment shows how to use a variable in a job definition. It is declaring a variable SQLDATA with the path to SQL Server backups and each day at midnight moves the backup files to an external device.

0 0 * * *  mv $SQLDATA/*.bak /mnt/

Cron Logging

Cron jobs are logged by default to the file /var/log/syslog, which is the file where all services log statuses and messages. There are different ways you can view its content.

The next script uses the grep command to search the pattern "cron" in the file /var/log/syslog (the -i is to make the search case insensitive).

grep -i cron /var/log/syslog
Searching cron entries on syslog.

Also, we can use a pipe to send the output to the tail command if we are interested only in the last 10 lines.

grep -i cron /var/log/syslog | tail
Showing the last 10 records of syslog entries.

The systemctl command shows the status of cron service as well as its last entries in syslog.

systemctl status cron
systemctl also shows the service status.

Also, we can use journalctl (a command that queries the system journal) and pass the system unit cron as a parameter. It has the peculiarity that shows system reboots.

journalctl -u cron
Querying the system journal.

Creating a cron.log File

We can create a single file that holds the cron messages by editing the /etc/rsyslog.d/50-default.conf file and uncommenting the line that starts with #cron.*. On the next image you can see that line.

Enabling the cron.log file.

After you uncomment that line and save the changes you should run the following command to restart the rsyslog service.

systemctl restart rsyslog

Crontab Security

Cron has two files, /etc/cron.allow and /etc/cron.deny that we can use to configure and manage the users allowed to have a personal crontab. The system-wide crontab will work regardless of the contents of those files. Those files have the same format which is one user per line. Here is how they work.

  • cron.allow - defines the users that are able to have their crontab file and therefore use cron.
  • cron.deny - contains all the users that are forbidden to have their crontab file and therefore use cron.
  • Depending on your distribution you may not have one of those files but you can create them.

In order to protect the system-wide crontab you have to set the proper permissions to the /etc/crontab file. The following command will give read and write permissions only for the root user and no other user will be able to read the file.

chmod 600 /etc/crontab

Crontab Shortcuts

Cron also has special folders that serve as shortcuts to schedule jobs at specific times. In the next table I enumerate those folders.

Folder Description
/etc/cron.daily The scripts on this folder run daily.
/etc/cron.hourly The scripts on this folder run hourly.
/etc/cron.monthly The scripts on this folder run monthly.
/etc/cron.weekly The scripts on this folder run weekly.

The system-wide crontab is what executes the content of each of those folders, so it may be useful to set those folders with root only permission to avoid the chance that a user puts malicious scripts in those folders.

If you want to know at which specific time the scripts in those folders will execute, you have to look at the /etc/crontab file. The next code is the content of my Ubuntu’s /etc/crontab file.

# m h dom mon dow user  command
17 *    * * *   root    cd / && run-parts --report /etc/cron.hourly
25 6    * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )
47 6    * * 7   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly )
52 6    1 * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )

Based on the previous example, contents of /etc/cron.hourly folder will start to execute at minute 17 every hour. Content of /etc/cron.weekly directory will run daily at 06:25 AM. The scripts in /etc/cron.weekly folder will run at 06:47 AM on Sunday each week. Finally, the scripts in /etc/monthly will execute at 06:52 AM the first day of the month.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms