How to change the Default Dump File location of SQL Server running on Linux Server

By: Manvendra Singh | Updated: 2017-07-03 | Comments | Related: > SQL Server on Linux

Problem

I need to update the configurations for my SQL Server instance running on Linux. I need to update the location where the memory and SQL dump files will be generated in case the instance crashes. Can you show me how to make these changes?

Solution

Changing the default memory and SQL dump directory in Linux based SQL Server is different as compared to Windows based SQL Server instances. Microsoft has provided a configuration script "mssql-conf" on Linux based SQL Servers to make these changes. "mssql-conf" script installs with SQL Server vNext installation on any Linux server.

Change the Default DUMP Directory for SQL Server running on Linux

Before going ahead, you need to have SQL Server vNext running on Red Hat Linux, SUSE Linux or Ubuntu server. Source of this method has been taken from Microsoft online documentation. Now let's start to proceed.

Step 1: First, I would recommend running a full backup of all your user and system databases to keep a copy in case anything goes wrong with SQL Server and you need to restore all your databases.

Step 2: Now connect to Linux server using PuTTY or login to the server locally. I connected to my Linux server where SQL Server vNext is running through PuTTY as shown below.

Step 3: The current default dump directory of SQL Server is /var/opt/mssql/log which is set during installation of SQL Server. We can check this location to see what else is stored under this location. Run ls -lrt /var/opt/mssql/log to display all files generated in this location.

#Display files of location /var/opt/mssql/log
sudo ls -lrt /var/opt/mssql/log

We can see all error log files, trc files are generated under this location /var/opt/mssql/log.

You can see the files generated under this location. I have captured a few top lines and bottom lines in the images which are output from the command. So whenever any memory dump will be created it will be generated under this location along with all these files. If you want that the memory dump file to be generated in a separate location then follow the remainder of the steps to get this done.

Step 4: Let's create a directory named "dump" under the tmp directory. Run the below command to create it. Also, I have checked and validated the newly created directory by running the ls -lrt command.

#create a directory named data under /opt/mssql/
sudo mkdir /tmp/dump

#check the newly created directory.
sudo ls -lrt /tmp

Once you execute the above command, the 'dump' directory will be created as shown in the below image. We have also executed ls -lrt to check the newly created directory.

Step 5: Now we will change the owner and group of this folder so that SQL Server can access this directory. Run the below command to change the owner of this directory.

#Run below command to change the owner and group of this directory to user "mssql"
sudo chown mssql /tmp/dump
sudo chgrp mssql /tmp/dump

change owner and group of directory to mssql

Step 5: Now we will use configuration script "mssql-conf" to set the default dump directory. This script installs under the bin directory with the SQL Server vNext installation. Next we will execute this script to change the default dump directory to /tmp/dump.

#execute below command to change the default dump directory.
sudo /opt/mssql/bin/mssql-conf set defaultdumpdir /tmp/dump

Once you execute the above command, it will set the default dump file location as per your choice asking you to restart the SQL Server service to apply this change on SQL Server. Run the below command to restart the SQL Server service.

#Restart SQL Server Service
systemctl restart mssql-server

Step 6: Now we will check whether SQL Server is running fine or not. We can connect using sqlcmd or we can check the SQL Server service as well. Run the below command to check the SQL Server service on this server after applying the above changes.

#check SQL Server Service
systemctl status mssql-server

We can see SQL Server service has come up successfully 30 seconds before so now go ahead and connect with the SQL Server instance using sqlcmd to validate more deeply. We are done with this change, whenever a memory dump file will be generated for this SQL Server instance it will be stored in new location: /tmp/dump.

Next Steps

The configuration script "mssql-conf" is very useful and you should explore more about this. I will write more tips on this utility in coming days.
Learn more about SQL Server vNext version and stay tuned for more tips on this topic.
Explore more knowledge on SQL Server Database Administration Tips.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2017-07-03