Rajendra Gupta

Monitoring Disk Space on Linux – Helpful Commands for SQL Server DBAs

December 25, 2018 by

In this article, we will explore the different Linux commands related to the disk space.

  1. ‘ls’ command
  2. ‘df’ command
  3. ‘du’ command

It is very important for SQL Database administrators to know about basic commands in Linux for any day-to-day work or troubleshooting purpose.

Disk space monitoring is must-know skill DBAs. We can monitor disk space using SQL Server DMV’s but we must also know to monitor at OS level. In a Windows environment, we can directly open the file explorer and view the files inside it along with the used space but this is different in Linux.

‘ls’ command in Linux

In Linux, we can use the ‘ls’ command to list the directories and the content of files. Most Linux users use this command frequently.

If we run the ‘ls’ command without any parameter, it lists out the files and directories. We do not get any other information such as size, file type, timestamp etc.

In Linux SQL Server, by default, all the data and log files go into ‘/var/opt/mssql/data’. First, go to this path using the command

# cd /var/opt/mssql/data

#ls

In the output above, we can see all the files listed there.

Let us run the command with -l parameter. ‘ls -l’ returns files, their size (in bytes), owner, permission and the timestamp.

By default, ls command returns the file size in bytes. It is not useful and easy to read the file size in bytes. Therefore, we can run the command ‘ls -lh’ to return the file size in human-readable format.

Command: ls -lh

In the output, you can notice that some of the files are in MB, KB or even in GB’s. The optimal unit for display is used by default.

We can sort out the results based on the modification time. The command ‘ls -lth’ returns the output with sorted data on the timestamp column in descending order.

Command: ls -lth

‘df’ command in Linux

We can use ‘df’ command to list out the available and used space on the file system in Linux. We also call this command ‘disk filesystem’ command.

If we do not specify any parameter with df command, it displays space on the current file system.

Command: df

Similar to ‘ls -h’ command, we can run the df command with ‘-h’ parameter to display results in human-readable format. By default, df command shows output file system size in KB’s. ‘df -h’ command automatically select the best unit to display for the particular file system. You can notice in the output file systems in MB, GB etc.

If we specifically want size in KB’s, run the below command

Command: df -k

Similarly, we can run the command ‘df -m’ to show file system size in MB’s.

We might need additional information about the file system type in the output. We can get this information with ‘df -T’ command.

We can get information about the disk space using any particular file as well. For example, if we specify the ‘tempdb.mdf’ along with the command, we get below output about the file system under which this file exists.

Command: df -h tempdb.mdf

‘du’ command in Linux

We can get disk usage of files and directories in the Linux system using the ‘du’ command. This command is a short form of disk usage.

In the below example, we can see the disk usage for the directory.

Similar to other commands, we can get the output in a human-readable format using the command ‘df -h’

We can get the total disk usage size for the underlying directory with the command ‘du -sh’. We can see in the out total 3.6GB.

We can get a breakdown of all the files and directories using the command ‘du -a’. In below output, we can see it lists out all the files under the /var/opt/mssql/data.

Again, we can convert the output in a human-readable format with the ‘-h’ parameter.

Command: du -ah

In the output, we see the files in various extensions. We can also exclude the files if we want for any particular extension. For example, in below screenshot, you can all files except *hkckp extension.

Command: du -ah –exclude “*hkckp” –specify the extension we want to exclude from the result.

You can get information about the particular file as well. For example, in below command, I have specified ‘tempdb.mdf’.

Command: du -ha “tempdb.mdf”

An alternate way to check Disk space with a SQL query

We can also check the disk space using the DMV ‘sys.dm_os_volume_stats’. We can join this DMV ‘sys.master_files’ to get details of the drive where DB files exist. We can only get information about the disk drive where we have the database files. We will not be able to monitor the other drives such as backup drive or disk mount points.

Connect to the SQL instance running on Linux and run the below query.

As highlighted in the screenshot, we have a few columns with NULL values. In windows system, we have the disk drive and the logical disk name for each drive or partition. Therefore, if we run this query in Windows SQL, we get the output for these columns as well. In Linux, we do not have disk drive similar to the windows OS. Therefore, these columns are appearing as NULL values.

Conclusion

In this article, we explored the important and useful command for the database administrators to monitor the disk space utilization. These commands provide details information about the directories, files and file system disk space available and the used space. You can also use a query to get some information about the disk space in Linux environment too. Be familiar with these commands in your environment.

Rajendra Gupta
Linux

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

3,024 Views