Silver Award WinnerSilver 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: https://docs.microsoft.com/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.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.