101 of Troubleshooting SQL Server on Linux

System logs:

\n

It contains the log messages that the system processes and applications, and these messages are written as plain text log files.

\n

The rsyslog service keeps various log files in the /var/log directory. You can open these files using native commands such as tail,head,more,less,cat, and so forth, depending on what you are looking for.

\n

In RHEL and SLES, they are called messages, while in Ubuntu, you would find them as syslog.

\n

RHEL and SLES: The primary system log file is located at /var/log/messages.

\n

Ubuntu: The main system log file is /var/log/syslog.

\n

Log files and journals are crucial to a system administrator's work. They reveal a great deal of information about a system and are instrumental during troubleshooting and auditing.

\n

For example, in RHEL, to display boot and other kernel messages, view /var/log/messages:

\n

[server]$ cat /var/log/messages

\n

Use grep and other filtering tools to gather more specific events from a file. You can also use tail to view files as they are updated.

\n

[server]$ tail -f /var/log/messages

\n

Check the /var/log/secure file to view users and their activities:

\n

[server]$ tail -f /var/log/secure

\n

Similarly in Ubuntu, we have/var/log/syslog

\n

What does the syslog or messages file contain? how do we read them?

\n

These log files contain events and messages generated by the kernel, applications, and users that log into the system.

\n

The logs are written in the below format.

\n

\n
The timestamp indicates the time when a log entry was created in the format MMM dd HH:mm:ss. Notice that this format does not include a year.
\n
\n
Hostname is the host or system that originally create the message.
\n
\n
Application is the application that created the message.
\n
\n
Message contains the actual details of an event.
\n

\n\n

\n

Since the message/syslog could contain thousands/millions of lines as log entries, using 'cat' command alone might not be the right choice at times. I suggest using grep for filtering text through the files.

\n

Using tail -f command allows you to read the current log file in real time. You may combine it with 'grep' to filter on desired text.

\n

An alternate method to validate the system events is via the journald

\n

Systemd-journald :

\n

journald is a component of systemd responsible for handling logging. It captures logs, records them, and makes them easy to find. Unlike traditional syslog implementations, journald offers features like structured logging, indexing for fast search, access control, and signed messages.

\n

The systemd-journald service does not keep separate files, as rsyslog does. The idea is to avoid checking different files for issues. Systemd-journald saves the events and messages in a binary format that cannot be read with a text editor. You can query the journal with the journalctl command.

\n

journald stores logs in memory (RAM) without persistent storage (by default), while the Traditional syslog (e.g., /var/log/messages) persists log data to flat files.

\n

To show all event messages, use:

\n

[server]$ journalctl

\n

To view journal entries for today, use:

\n

[server]$ journalctl --since today

\n

To check for messages related to the sql server service for the past hour, you can run:

\n

journalctl --unit mssql-server.service --since \"1 hour ago\"

\n

SQL Server Error logs:

\nThe error log contains informational messages, warnings, and information about critical events. The error log also contains information about user-generated messages and auditing information such as logon events (success and failure).In Linux the default SQL Errorlog location is /var/opt/mssql/log\n

(Keep in mind that you will require superuser permissions to traverse these folders and logs.)

\n

This is also the default location for XEvents, dump files and trace files.

\n

Comparing Error and System Logs Output

\nYou can use both the SQL Server error log and the system logs to identify the cause of problems. For example, while monitoring the SQL Server error log, you may encounter error messages that do not contain cause information. By comparing the dates and times for events between these logs, you can narrow the list of probable causes.
Consider the following scenario: We receive an alert indicating that the application is unable to connect to SQL following a maintenance activity over the weekend. We log into the server to verify if SQL Server is operational.

\n

Upon inspection, we find that the SQL process is not running. We then check the status and attempt to start the SQL Server to see if it comes online.

\n

On restarting SQL Server, we observe the following information in the error logs.

\n

Next, we need to examine the system event logs, specifically the messages file in RHEL & SLES or the syslog file in Ubuntu, where events are recorded.We can use the following command to retrieve service logs from the recent boot:\n

\n

journalctl --unit mssql-server.service --boot --no-pager

\n

By validating the messages or syslog file within the same timeframe, we gather additional information.

\n

\nFrom the logs, we identify a permission issue on a specific folder or file that is preventing SQL Server from starting. Granting the necessary permissions should resolve the issue.\n

\n

Task Manager:

\nWhere is the Task Manager in Linux? There are command-line utilities that provide similar information to what we see in Windows. We will explore the top and htop utilities\n

top

\n

The first line of numbers on the dashboard includes the time, how long your computer has been running, the number of people logged in, and what the load average has been for the past one, five, and 15 minutes. The second line shows the number of tasks and their states: running, stopped, sleeping, or zombie.The next 3 lines describe CPU, Memory utilization, Swap Memory of the server.
The column details of the process are tabulated as below.\n

\n

To get into the details of a particular process, (In our case SQL Server) we can use the PID to get the further details of the SQL and the tasks that SQL is currently running.
To get the child PID of SQLServer, use this short command and run the top command on the pid.\n

pidof sqlservr | cut -d' ' -f1

\n

top -p (pid output of the above command)

\n

We can also get them into single command as shown below,

\n

top -p $(pidof sqlservr | cut -d' ' -f1)

\n

We see the utilization of various tasks within SQL Server, that are consuming the resources.

\n

Another utility that provides similar information is htop. The key difference is that htop offers a more user-friendly experience with its use of colors and graphs, compared to the top command.

\n

\nWhile top and htop provide valuable information, there are additional command-line tools such as vmstat, System Activity Information (sar), iostat, and others. I suggest running these commands on your test machines and monitoring the output to become familiar with checking performance metrics on a Linux server.
References: \n

SLES: https://documentation.suse.com/sles/12-SP5/html/SLES-all/cha-util.html#

\n

RHEL:https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/7/html/performance_tuning_guide/sect-red_hat_enterprise_linux-performance_tuning_guide-performance_monitoring_tools-built_in_command_line_tools

\n

Ubuntu: https://manpages.ubuntu.com/manpages/jammy/man1/top.1.html#2.%20summary%20display

\n

I hope this serves as a helpful introduction to basic troubleshooting for SQL on Linux.

\n