In this article, we will explore the different Linux commands related to the disk space.
- ‘ls’ command
- ‘df’ command
- ‘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.
- In my previous article ‘Using the ‘top’ Command in Linux for SQL Server DBAs’, we learned the ‘top’ command to show the CPU and Memory utilization. It also displays the user and system processes.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023