SQL Server – How to Get Query Timing with sqlcmd

This ended up as a case of not being careful when working as root.

I had been researching if SQLCLR on Linux would have access to the app.Config file like it does in Windows (sadly, it does not: SQL Server 2017 on Linux ignores app config file if it exists, or sometimes locks-up if it doesn't (SQLCLR) ) and under certain circumstances SQL Server would completely lock up. When that happened, the only way to stop it was to do a kill -9 on sqlservr. One of the times that I was starting the service again, I did so by directly executing /opt/mssql/bin/sqlservr and while I was working as root (hence the process itself was owned by root).

There were no immediate errors or odd behavior resulting from running sqlservr as root, BUT when the VM restarted and SQL Server attempted to start properly (i.e. running as the mssql user), that is when it got stuck at the very beginning.

I found that a direct consequence of running sqlservr as root was that the /var/opt/mssql/log/errorlog file (and some others that are created upon SQL Server starting) were owned by root (makes sense).

And, a direct consequence of those files being owned by root is that when the process is started properly (as mssql), then the mssql user doesn't have permission to rename the file to end in .1 (and whatever else needs to happen with any other files, such as default trace, etc). However, rather than getting a permissions error, it just hangs forever.

The primary fix is to simply run the following as root (I haven't tried running it as mssql). For both of the following commands, sudo is only needed when not currently acting as root as it will run the command that follows it as root (or some other user if you specify -u username), after being prompted to enter the root password.

sudo chown -R  mssql:mssql /var/opt/mssql

The secondary fix (to make sure that this doesn't happen again), is to start SQL Server properly ;-):

sudo systemctl start mssql-server

Yes, we had a similar issue as well--for payroll data! Try the default trace as shown in this blog (it appears that this is available for Linux as well):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/80938ab2-28a4-46eb-8b09-84ab9ae22079/how-to-find-the-user-who-performed-a-truncate-statement?forum=transactsql

Linux default trace location:

http://www.dharmendrakeshari.com/main-file-directory-structure-sql-server-linux/