MSSQL Server for Linux: Troubleshooting
Introduction:
Normally there is issue or user error see in MSSQL, we check using operating system.Dba should aware of some Linux command.Commands:
We need to check whether SQL services is running which process id and CPU utilization and read error log.
1.check SQL error log
pwd:-cd /var/opt/mssql/log
2.list error log file in MSSQL
# 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
# 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. All rights reserved.
Developer Edition (64-bit) on Linux (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 All rights reserved.
2017-08-15 20:28:43.68 Server Server process ID is 4120.
2017-08-15 20:28:43.68 Server Logging SQL Server messages in file '/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 with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2017-08-15 20:28:43.70 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2017-08-15 20:28:43.70 Server Detected 3194 MB of RAM. This is an informational message; no user action is required.
2017-08-15 20:28:43.70 Server Using conventional memory in the memory manager.
2017-08-15 20:28:44.03 Server Buffer pool extension is already disabled. No action is necessary.
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 of dedicated administrator connections for this 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 of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2017-08-15 20:28:44.35 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2017-08-15 20:28:44.36 Server In-Memory OLTP initialized on lowend machine.
2017-08-15 20:28:44.54 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2017-08-15 20:28:44.55 Server Query Store settings initialized with enabled = 1,
2017-08-15 20:28:44.56 spid6s Starting up database 'master'.
2017-08-15 20:28:44.56 Server Software Usage Metrics is disabled.
2017-08-15 20:28:45.13 spid6s 10 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2017-08-15 20:28:45.24 spid6s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2017-08-15 20:28:45.25 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2017-08-15 20:28:45.46 spid6s Buffer pool extension is already disabled. No action is necessary.
2017-08-15 20:28:45.47 spid6s Resource governor reconfiguration succeeded.
2017-08-15 20:28:45.47 spid6s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2017-08-15 20:28:45.47 spid6s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2017-08-15 20:28:45.65 spid6s SQL Trace ID 1 was started by login "sa".
2017-08-15 20:28:45.69 spid6s Server name is 'DBA04'. This is an informational message only. No user action is required.
4.check sql server is running or not
# sudo systemctl status mssql-server
mssql-server.service - Microsoft SQL Server Database Engine
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 day 12h 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: Some lines were ellipsized, use -l to show in full.
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 is process id running for mssql.
6.top command
--> show information like tasks,memory,cpu and swap.# top
top - 09:29:24 up 119 days, 21:47, 3 users, load average: 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 USER PR 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 is highlighted in yellow color.
7. grep command
-->The grep command searches the given input files for lines containing a match or a text string. Following example where find process which is word related MSSQL.
ps -ef | grep mssqlmssql 36817 1 0 Jul28 ? 00:00:00 /opt/mssql/bin/sqlservrmssql 36851 36817 1 Jul28 ? 04:46:58 /opt/mssql/bin/sqlservrroot 55572 55513 0 19:54 pts/0 00:00:00 grep --color=auto mssql |
8.Check mssql services packages is 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
# 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 mssql9. check package information of mssql
# yum info mssql-server Loaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: centos.mirror.net.in * epel: kartolo.sby.datautama.net.id * extras: centos.excellmedia.net * updates: centos.excellmedia.netInstalled PackagesName : mssql-serverArch : x86_64Version : 14.0.900.75Release : 1Size : 870 MRepo : installedFrom repo : packages-microsoft-com-mssql-serverSummary : Microsoft SQL Server Relational Database EngineLicense : CommercialDescription : The mssql-server package contains the Microsoft SQL Server Relational Database Engine.10. Check file system
mount | grep "^/dev" --check file system must be XFS or EXT4. 
