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

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

System Status Report for SQL Server Running on Linux


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

Problem

You just restarted your Linux server. Is everything OK? Did all the key processes start up? Are the SQL Server databases OK? In this tip we will look at a process you can put in place to let you know the status of your server.

Solution

This tip has a Linux shell script that can send you an email with a status report for your server after a restart.  This way you can be sure that everything is running as required.

Example Email Report for Linux Server Status

Here is an example of the email that will be generated.

sample email

Configure Email

You will need to configure email on your system first. Here is a link to configuring email on Ubuntu and here is a link to configure email on SUSE.

Find Location of sqlcmd on Server

As we would like to run sqlcmd on boot up, we will need the full path of the sqlcmd utility.

To find where it is located issue this command:

whereis sqlcmd

The response will be something like this:

sqlcmd: /opt/mssql-tools/bin/sqlcmd

Use that full path in the script below.

Linux Script to Generate Server Status Report

Here is a step by step guide inside the script.

#!/bin/bash 
# File name : status_report.sh
# Author Graham Okely B app Sc (IT)
 
# Comments begin with a #
# Wait for the SQL Server services to commence before running the report.
# m is minutes, test this you may need to add more delay time on bigger systems.
sleep 1m 
 
# Create the report file in the /tmp directory as the /tmp area is cleaned on boot up.
# The $$ means the process number
REPORT=/tmp/status_report.$$
 
# Commence the report with the server host name.
SERVER=`hostname`
# The echo -n means no carriage return, joining the follow line.
# A single > means wipe the file and add output to the file
echo -n 'Report for host : ' > $REPORT
# A double >> means append data to the file
echo $SERVER >> $REPORT
echo '' >> $REPORT
 
date >>$REPORT
# Two single quotes will add a line feed on the report
echo '' >> $REPORT
echo -n 'Current user : ' >> $REPORT
 
# The whoami comments gives the current username
whoami  >> $REPORT
echo '' >> $REPORT
 
echo 'Disk Free Space' >> $REPORT
echo '' >> $REPORT
# Get the disk free and sort it by smallest free space to most free space
# The df command shows us the disk space free
# -h Means human readable format please.
# Use the awk command to arrange the columns into the most important on the left.
# Use the sort command to order the report so that the LEAST FREE space is first.
# That means you only need to spot the upper left value to see the least free drive.
# Use grep to remove lines and put the report into a simple format.
df -h --total | awk '{printf " %-10s %-10s %-10s %-10s %-20s %-35s \n", $5,$2,$3,$4,$6,$1}' | grep Use >>$REPORT
df -h --total | awk '{printf " %-10s %-10s %-10s %-10s %-20s %-35s \n", $5,$2,$3,$4,$6,$1}' | grep -v Use | grep -v total | sort -nr >>$REPORT
df -h --total | awk '{printf " %-10s %-10s %-10s %-10s %-20s %-35s \n", $5,$2,$3,$4,$6,$1}' | grep total >>$REPORT
 
 
echo '' >> $REPORT
echo 'SQL Server Status' >> $REPORT
echo 'systemctl status mssql-server -q | head -5 ' >> $REPORT
# The systemctl command gives us a report on Linux systems.
systemctl status mssql-server -q | head -5 >>$REPORT
 
echo '' >> $REPORT
echo -n 'Users logged in:' >>$REPORT
who  >>$REPORT
 
echo '' >> $REPORT
# List databases available
echo 'Databases :' >>$REPORT
 
# You can add commands here as you see fit.
# This lists the databases available.
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P XXXXX -Q "Select [name] from sys.databases Order by [name]" | tail -n +3 >>$REPORT
# Show total space used
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P XXXXX -Q " Select Convert(Decimal(10,2),(Sum(size * 8.00)/1024.00/1024.00)) As GB_Total From master.sys.master_files" >>$REPORT
 
# Show the report, comment this out when automating the report.
cat $REPORT
 
# Email the report
# Use the host name in the email subject line so it easy to see which server rebooted.
cat $REPORT | mail -s $SERVER [email protected]_host.com
 
# Clean up the report
rm $REPORT

Tips:

The #!/bin/bash is a convention in script writing. It shows where the shell you are using is located.

The other lines beginning with # are comment lines. Name your scripts at the top and save them first in an organized folder. Then add the code. I find that order of doing things clarifies your scripts purpose and keeps you on track.

Bootup

There are a few ways to configure Linux to run a command on bootup. When using Ubuntu a simple method is to add a line to the crontab file. The crontab file is a special file that holds a schedule of tasks for each user. Usually a crontab entry follows specific rules of format. However, in the case of a reboot we can add a non-standard format of:

@reboot /home/graham/bin/status_report.sh

Use this command to edit the root crontab file.

sudo crontab -e

See this link for boot up command configuration on SUSE Linux.

Conclusion

Adjust the status report with more commands to make it work for you. Remember to place a general monitoring email address as the email target, so that when you go on leave your colleagues will know when that server is rebooted.

Tip: Sometimes we spend a long time creating an automation tool. If you forget you created, debugged, tested and used it then you lose the efficiency gained by automation. A tip to help you remember is to create some system to remind you.

Reminder tip #1 Each time you log in get a list of your automation tools.

Add this command to the bottom of your ~/.bashrc file.

ls -l ~/bin

Reminder Tip #2 Add your new command to a Wiki page entry, a summary page or something like this register.

Automation Summary Register

# Location Command Parameters  Description
1 ~/bin (When logged in as joe) load_database.sh Database_Name Restores the most recent backup in /files/secured/Backups/Database_Name
To the default instance on localhost.
2 ~/bin (When logged in as smith) Connect.sh None The very simplest way I can check if the SQL Server instance is running.
3 ~/bin fix_account.sh Database_Name user_name Fixes orphans and adds executor role.
4 ~/bin status_report.sh None Emails a system report.
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

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