Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > SQL Server on Linux

Attend these FREE MSSQLTips webcasts >> click to register


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.

connect to Linux Server

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.

ls -lrt /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.

create the directory dump under /tmp/

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

set default dump location

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

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

check 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.


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools