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

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

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


By:   |   Last Updated: 2018-10-18   |   Comments   |   Related Tips: More > SQL Server on Linux

Problem

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.

Solution

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:

HOME=/home/daniel
PATH=HOME:/bin/:/usr/bin

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:

HOME=/home/daniel
PATH=/home/daniel:/bin/:/usr/bin

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.

SQLDATA=/var/opt/mssql/data
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


Last Updated: 2018-10-18


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