SQL Server for Linux: Troubleshooting (Part 1)

Article
1/17/2024

https://msdnshared.blob.core.windows.net/media/2016/08/0841.NinjaAwardTinySilver.pngSilver Award Winner

Introduction

Normally, if there is some issue or user error in MSSQL, we check the operating system. DBA should be aware of some Linux commands.

Commands

We need to check whether SQL services are running, which process id and CPU utilization and read error log.

1. Check SQL error log

pwd prints the full path-name of the current working directory.

pwd:-cd /var/opt/mssql/log

#pwd

2. List error log file in MSSQL

ls lists the files contained in the current directory, sorted in alphabetical order. Error log starts with Err so we will check starting with err files.

# ls err* ----

errorlog errorlog.10 errorlog.12 errorlog.14 errorlog.16 errorlog.18 errorlog.2 errorlog.21 errorlog.23 errorlog.3 errorlog.5 errorlog.7 errorlog.9

errorlog.1 errorlog.11 errorlog.13 errorlog.15 errorlog.17 errorlog.19 errorlog.20 errorlog.22 errorlog.24 errorlog.4 errorlog.6 errorlog.8

3. head command

The head command reads the first few lines of any text given to it as an input and writes them to standard output (which, by default, is the display screen). #

head -50 errorlog --> Display 50 lines from errorlog

2017-08-15 20:28:43.67 Server Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)

Apr 13 2017 11:44:40

Copyright (C) 2017 Microsoft Corporation. Allrights reserved.

Developer Edition (64-bit) onLinux (CentOS Linux 7 (Core))

2017-08-15 20:28:43.67 Server UTC adjustment: 5:30

2017-08-15 20:28:43.67 Server (c) Microsoft Corporation.

2017-08-15 20:28:43.68 Server Allrights reserved.

2017-08-15 20:28:43.68 Server Server process ID is4120.

2017-08-15 20:28:43.68 Server Logging SQL Server messages infile '/var/opt/mssql/log/errorlog'.

2017-08-15 20:28:43.68 Server Registry startup parameters:

-d /var/opt/mssql/data/master.mdf

-l /var/opt/mssql/data/mastlog.ldf

-e /var/opt/mssql/log/errorlog

2017-08-15 20:28:43.69 Server SQL Server detected 1 sockets with4 cores per socket and4 logical processors per socket, 4 total logical processors; using 4 logical processors based onSQL Server licensing. This isan informational message; nouseractionisrequired.

2017-08-15 20:28:43.70 Server SQL Server isstarting atnormal priority base (=7). This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:43.70 Server Detected 3194 MB ofRAM. This isan informational message; nouseractionisrequired.

2017-08-15 20:28:43.70 Server Using conventional memory inthe memory manager.

2017-08-15 20:28:44.03 Server Buffer pool extension isalready disabled. Noactionisnecessary.

2017-08-15 20:28:44.26 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled.

2017-08-15 20:28:44.26 Server Implied authentication manager initialization failed. Implied authentication will be disabled.

2017-08-15 20:28:44.28 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384'].

2017-08-15 20:28:44.34 Server The maximum number ofdedicated administrator connections forthis instance is'1'

2017-08-15 20:28:44.34 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description ofthe NUMA configuration forthis computer. This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:44.35 Server Using dynamiclock allocation. Initial allocation of2500 Lock blocks and5000 Lock Owner blocks per node. This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:44.36 Server In-Memory OLTP initialized onlowend machine.

2017-08-15 20:28:44.54 Server DatabaseInstant File Initialization: enabled. Forsecurity andperformance considerations see the topic 'Database Instant File Initialization'inSQL Server Books Online. This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:44.55 Server Query Store settings initialized withenabled = 1,

2017-08-15 20:28:44.56 spid6s Starting up database'master'.

2017-08-15 20:28:44.56 Server Software Usage Metrics isdisabled.

2017-08-15 20:28:45.13 spid6s 10 transactions rolled forwardindatabase'master'(1:0). This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:45.24 spid6s 0 transactions rolled back indatabase'master'(1:0). This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:45.25 spid6s Recovery iswriting a checkpointindatabase'master'(1). This isan informational message only. Nouseractionisrequired.

2017-08-15 20:28:45.46 spid6s Buffer pool extension isalready disabled. Noactionisnecessary.

2017-08-15 20:28:45.47 spid6s Resource governor reconfiguration succeeded.

2017-08-15 20:28:45.47 spid6s SQL Server Audit isstarting the audits. This isan informational message. Nouseractionisrequired.

2017-08-15 20:28:45.47 spid6s SQL Server Audit has started the audits. This isan informational message. Nouseractionisrequired.

2017-08-15 20:28:45.65 spid6s SQL Trace ID 1 was started bylogin "sa".

2017-08-15 20:28:45.69 spid6s Server nameis'DBA04'. This isan informational message only. Nouseractionisrequired.

4. Check SQL server is running or not

sudo systemctl status --> get status of service

#sudo systemctl status mssql-server

mssql-server.service - Microsoft SQL Server DatabaseEngine

Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)

Active: active (running) since Tue 2017-08-15 20:28:31 IST; 1 day12h ago

Docs: /en-us/sql/linux

Main PID: 56656 (sqlservr)

CGroup: /system.slice/mssql-server.service

├─56656 /opt/mssql/bin/sqlservr

└─56699 /opt/mssql/bin/sqlservr

Aug 15 20:28:47 DBA04 sqlservr[56656]: 2017-08-15 20:28:47.20 spid6s ....

Aug 15 20:28:47 DBA04 sqlservr[56656]: 2017-08-15 20:28:47.21 spid6s ....

Aug 15 20:28:48 DBA04 sqlservr[56656]: 2017-08-15 20:28:48.78 spid9s ....

Aug 15 20:28:49 DBAT04 sqlservr[56656]: 2017-08-15 20:28:49.32 spid9s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.36 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.44 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.62 spid6s ....

Aug 15 20:28:55 DBA04 sqlservr[56656]: 2017-08-15 20:28:55.21 Logon ....

Aug 15 20:28:55 DBA04 sqlservr[56656]: 2017-08-15 20:28:55.21 Logon ...]

Hint: Somelines were ellipsized, use -l toshow infull.

5. Process running MSSQL

# ps -ef| grep mssql

root 48427 48368 0 08:50 pts/0 00:00:00 grep --color=auto mssql

mssql 56656 1 0 Aug15 ? 00:00:00 /opt/mssql/bin/sqlservr

mssql 56699 56656 1 Aug15 ? 00:23:26 /opt/mssql/bin/sqlservr

56699 isprocess id running formssql.

6. Top command

--> show information like tasks, memory, CPU and swap.

# top

top- 09:29:24 up 119 days, 21:47, 3 users, loadaverage: 0.03, 0.21, 0.30

Tasks: 238 total, 1 running, 235 sleeping, 2 stopped, 0 zombie

%Cpu(s): 0.4 us, 0.3 sy, 0.0 ni, 95.3 id, 3.9 wa, 0.0 hi, 0.0 si, 0.0 st

KiB Mem : 4088260 total, 73712 free, 1553332 used, 2461216 buff/cache

KiB Swap: 8191996 total, 8033108 free, 158888 used. 1353632 avail Mem

PID USERPR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

42936 oracle -2 0 1488876 16912 14236 S 2.0 0.4 53:54.96 ora_vktm_magic

56699 mssql 20 0 4069136 536168 2640 S 1.3 13.1 23:50.96 sqlservr

42994 oracle 20 0 1489488 41560 38508 S 1.0 1.0 0:36.71 ora_mmnl_magic

49435 root 20 0 157840 2380 1564 R 0.7 0.1 0:00.03 top

57044 mongod 20 0 1078456 37520 2152 S 0.7 0.9 61:40.10 mongod

3274 root 20 0 0 0 0 S 0.3 0.0 0:00.97 kworker/3:1

1 root 20 0 191004 2920 1424 S 0.0 0.1 27:26.09 systemd

2 root 20 0 0 0 0 S 0.0 0.0 0:00.03 kthreadd

3 root 20 0 0 0 0 S 0.0 0.0 1:52.46 ksoftirqd/0

5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H

7 root rt 0 0 0 0 S 0.0 0.0 0:08.03 migration/0

8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh

CPU utilization ishighlighted inyellow color.

7. grep command

-->The grep command searches the given input files for lines containing a match or a text string.

Following example finds a process which is word related MSSQL.

ps -ef | grep mssql

mssql 36817 1 0 Jul28 ? 00:00:00 /opt/mssql/bin/sqlservr

mssql 36851 36817 1 Jul28 ? 04:46:58 /opt/mssql/bin/sqlservr

root 55572 55513 0 19:54 pts/0 00:00:00 grep --color=auto mssql

Note

grep –r “function” * --> seach sting recursively in all directories

grep -n "string" filenamet --> Show line number while displaying the output using grep -n.

8. Check mssql services packages are installed

#ps -ef | grep mssql

mssql 36817 1 0 Jul28 ? 00:00:00 /opt/mssql/bin/sqlservr

mssql 36851 36817 1 Jul28 ? 04:46:58 /opt/mssql/bin/sqlservr

root 55572 55513 0 19:54 pts/0 00:00:00 grep --color=auto mssql

9. Check package information of mssql

yum info package-- You would like to know information of a package before installing it. To get information of a package just issue.

#yum info mssql-server

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: centos.mirror.net.in

* epel: kartolo.sby.datautama.net.id

* extras: centos.excellmedia.net

* updates: centos.excellmedia.net

Installed Packages

Name : mssql-server

Arch : x86_64

Version : 14.0.900.75

Release : 1

Size : 870 M

Repo : installed

From repo : packages-microsoft-com-mssql-server

Summary : Microsoft SQL Server Relational Database Engine

License : Commercial

Description : The mssql-server package contains the Microsoft SQL Server Relational Database Engine.

10. Check file system

mount command is used to mount a file system in Linux, it can also be used to mount an ISO image, mount remote Linux filesystem and so much more.

When run without any arguments, it prints info about disk partitions including the file system type.

#mount | grep "^/dev "

-->check file system must be XFS or EXT4.

11. Check IP of the server

The “ifconfig” command is used for displaying current network configuration information, setting up an ip address, netmask or broadcast address to an network interface, creating an alias for network interface, setting up hardware address and enable or disable network interfaces.

#ifconfig

12. Check port use for mssql

netstat is one of the most basic network service debugging tools, telling you what ports are open and whether any programs are listening on ports.

#netstat -tulpn | grep LISTEN

13.CPU information use for mssql

# cat /proc/cpuinfo

-->Cpu information

Some examples which help.

cat /proc/cpuinfo | grep 'vendor' | uniq #view vendor name
$ cat /proc/cpuinfo | grep 'model name' | uniq #display model name
$ cat /proc/cpuinfo | grep processor | wc -l #count the number of processing units
$ cat /proc/cpuinfo | grep 'core id' #show individual cores

14.Current uptime use for server

# uptime

-->Show current uptime

15.Who you are logged in as?

#whoami

->Who you are logged in as

16. Free space usages

df

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/mapper/vg1-root_lv 43129200 38581160 2334152 95% /

devtmpfs 499608 0 499608 0% /dev

tmpfs 505472 0 505472 0% /dev/shm

tmpfs 505472 51000 454472 11% /run

tmpfs 505472 0 505472 0% /sys/fs/cgroup

/dev/sda1 499656 81652 381308 18% /boot

/dev/mapper/vg1-tmp_lv 9947976 36956 9382636 1% /tmp

tmpfs 101096 0 101096 0% /run/user/0

Note

df -a -->display all system files along with those which have zero block sizes

df -h --> make df command display the output in human-readable format.

df -total-->produce total for a size, used and available columns in the output.

17.Show directory space usages

#du

--->Show directory space usage

18.Show directory space usages

#cat /proc/meminfo

--> check memory information

19. Check kernel information

# uname -a

-->Check kernel information

Other Resources

This Article Participated in TechNet Guru Competition October 2017 and won Silver Medal.

Share via