By: Daniel Farina | Updated: 2017-09-13 | Comments (7) | Related: > Monitoring
Problem
As you know, disk space is something that really matters for SQL Server and if you don’t keep a close eye on available disk resources it could cause database failures. In this tip I will show you an easy way to make a report of disk usage for all of your databases on all your servers and have the report delivered via email.
Solution
As I said above, disk space is a serious matter. If you work at a big company and run out of disk space you can’t just go to a store and buy a hard drive; you need to create a purchase order, that order must go into a workflow for approval and of course it takes time to get delivered and installed. But when you don’t have available storage for your databases, time is something you cannot afford. This is one of the motives you must be aware of for your servers' available disk space. The other is capacity planning. If a Project Manager comes to you asking about the company’s current storage capabilities for a new project you need to show that you are aware and in control of the platform, so you can give the right answer.
In my previous tips I showed what sqlcmd is capable of using variables, both internal and user defined, and some of the sqlcmd commands. This tip shows a practical example of something you can do by using the concepts of my previous tips.
Taking Advantage of sqlcmd
If you have read my previous tips about sqlcmd, you know that it allows us to change server connections inside the script. In other words, you can write a sqlcmd script that runs on all the servers of your infrastructure.
Based on this, we can execute a command sequence that returns database disk usage on all our servers with just one script. Of course, if we aim to send the results by email in HTML format we must use additional tools to help with the formatting.
T-SQL Script to Get Disk Space Information
The following script shows the most basic information regarding disk usage by database, like autogrowth, database file size and available disk space and so on.
USE master;
GO
CREATE TABLE #Temp
(
[Server] [varchar] (128) NULL,
[Database] [varchar] (128) NULL,
[File Name] [sys].[sysname] NOT NULL,
[Type] [varchar] (60) NULL,
[Path] [varchar] (260) NULL,
[File Size] [varchar] (53) NULL,
[File Used Space] [varchar] (53) NULL,
[File Free Space] [varchar] (53) NULL,
[% Free File Space] [varchar] (51) NULL,
[Autogrowth] [varchar] (53) NULL,
[volume_mount_point] [varchar] (256) NULL,
[Total Volume Size] [varchar] (53) NULL,
[Free Space] [varchar] (53) NULL,
[% Free] [varchar] (51) NULL
)
EXEC sp_MSforeachdb ' USE [?];
INSERT INTO #Temp
SELECT @@SERVERNAME [Server] ,
DB_NAME() [Database] ,
MF.name [File Name] ,
MF.type_desc [Type] ,
MF.physical_name [Path] ,
CAST(CAST(MF.size / 128.0 AS DECIMAL(15, 2)) AS VARCHAR(50)) + '' MB'' [File Size] ,
CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 )) AS VARCHAR(50)) + '' MB'' [File Used Space] ,
CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0) AS VARCHAR(50)) + '' MB'' [File Free Space] ,
CAST(CONVERT(DECIMAL(10, 2), ( ( MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ) / ( MF.size / 128.0 ) ) * 100) AS VARCHAR(50)) + ''%'' [% Free File Space] ,
IIF(MF.growth = 0, ''N/A'', CASE WHEN MF.is_percent_growth = 1 THEN CAST(MF.growth AS VARCHAR(50)) + ''%''
ELSE CAST(MF.growth / 128 AS VARCHAR(50)) + '' MB''
END) [Autogrowth] ,
VS.volume_mount_point ,
CAST(CAST(VS.total_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50))
+ '' GB'' [Total Volume Size] ,
CAST(CAST(VS.available_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50))
+ '' GB'' [Free Space] ,
CAST(CAST(VS.available_bytes / CAST(VS.total_bytes AS DECIMAL(20, 2))
* 100 AS DECIMAL(20, 2)) AS VARCHAR(50)) + ''%'' [% Free]
FROM sys.database_files MF
CROSS APPLY sys.dm_os_volume_stats(DB_ID(''?''), MF.file_id) VS
'
SELECT * FROM #Temp
DROP TABLE #Temp
As you can see above, I am using the undocumented sp_MSforeachdb system stored procedure which basically runs a given script on all the databases. On the following screen capture you can see the results of the previous script execution for all databases in this instance.
Enhanced Script to Create HTML Formatted Output
We have a script that shows disk space usage, but we need a way to create the results in an HTML format. There are two possibilities, we can create a report with one table per sever or merge all the data into one single table. For this solution, I will send all servers data to one single table and use this for the final email that is sent.
We must adjust the previous query to return results in HTML format. I opted for the simplest approach, which is concatenating the HTML tags to the query columns as shown below.
SET NOCOUNT ON
CREATE TABLE #Temp
(
[Server] [varchar] (128) NULL,
[Database] [varchar] (128) NULL,
[File Name] [sys].[sysname] NOT NULL,
[Type] [varchar] (60) NULL,
[Path] [varchar] (260) NULL,
[File Size] [varchar] (53) NULL,
[File Used Space] [varchar] (53) NULL,
[File Free Space] [varchar] (53) NULL,
[% Free File Space] [varchar] (51) NULL,
[Autogrowth] [varchar] (53) NULL,
[volume_mount_point] [varchar] (256) NULL,
[Total Volume Size] [varchar] (53) NULL,
[Free Space] [varchar] (53) NULL,
[% Free] [varchar] (51) NULL
)
EXEC sp_MSforeachdb ' USE [?];
INSERT INTO #Temp
SELECT @@SERVERNAME [Server] ,
DB_NAME() [Database] ,
MF.name [File Name] ,
MF.type_desc [Type] ,
MF.physical_name [Path] ,
CAST(CAST(MF.size / 128.0 AS DECIMAL(15, 2)) AS VARCHAR(50)) + '' MB'' [File Size] ,
CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 )) AS VARCHAR(50)) + '' MB'' [File Used Space] ,
CAST(CONVERT(DECIMAL(10, 2), MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0) AS VARCHAR(50)) + '' MB'' [File Free Space] ,
CAST(CONVERT(DECIMAL(10, 2), ( ( MF.size / 128.0 - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ) / ( MF.size / 128.0 ) ) * 100) AS VARCHAR(50)) + ''%'' [% Free File Space] ,
IIF(MF.growth = 0, ''N/A'', CASE WHEN MF.is_percent_growth = 1 THEN CAST(MF.growth AS VARCHAR(50)) + ''%''
ELSE CAST(MF.growth / 128 AS VARCHAR(50)) + '' MB''
END) [Autogrowth] ,
VS.volume_mount_point ,
CAST(CAST(VS.total_bytes / 1024 / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50))
+ '' GB'' [Total Volume Size] ,
CAST(CAST(VS.available_bytes / 1024. / 1024 / 1024 AS DECIMAL(20, 2)) AS VARCHAR(50))
+ '' GB'' [Free Space] ,
CAST(CAST(VS.available_bytes / CAST(VS.total_bytes AS DECIMAL(20, 2))
* 100 AS DECIMAL(20, 2)) AS VARCHAR(50)) + ''%'' [% Free]
FROM sys.database_files MF
CROSS APPLY sys.dm_os_volume_stats(DB_ID(''?''), MF.file_id) VS
'
--SELECT * FROM #Temp
SELECT '<TR>' +
'<TD>' + [Server] + '</TD>' +
'<TD>' + [Database] + '</TD>' +
'<TD>' + [File Name] + '</TD>' +
'<TD>' + Type + '</TD>' +
'<TD>' + Path + '</TD>' +
'<TD>' + [File Size] + '</TD>' +
'<TD>' + ISNULL([File Used Space], 'N/A') + '</TD>' +
'<TD>' + ISNULL([File Free Space], 'N/A') + '</TD>' +
'<TD>' + ISNULL([% Free File Space], 'N/A') + '</TD>' +
'<TD>' + Autogrowth + '</TD>' +
'<TD>' + volume_mount_point + '</TD>' +
'<TD>' + [Total Volume Size] + '</TD>' +
'<TD>' + [Free Space] + '</TD>' +
'<TD>' + [% Free] + '</TD>' +
'</TR>'
FROM #Temp
DROP TABLE #Temp
GO
The previous query will give you a result similar to the following screen capture.
For those of you that don’t know much about HTML, the image above shows that each output row is formatted as a table row in HTML format. This is our starting point. The HTML is not completely formatted the way we need it, but we will fix in the sections below.
The above code should be saved into a file named query.sql, this will be used in the next steps.
SQLCMD File to Create Report
Now we must run the previous script on all our servers. To this, I created a new sqlcmd script file named serv.sql which contains the following code. This will run the query.sql file against three of my SQL Servers.
I am running this locally on SQLUX.ATLAS.COM, so for the first query I am connecting locally that is why I did not specify a server.
:r ./query.sql GO :CONNECT SQL-A.ATLAS.COM :r ./query.sql GO :CONNECT SQL-B.ATLAS.COM :r ./query.sql GO
In order to help you to understand the previous sqlcmd script, the information below about my test environment may be useful.
Also, I think that maybe a more graphical representation of the previous script can help even more.
If you had a chance to read my previous tips about sqlcmd, you may understand that on the script above I am using two sqlcmd commands :r and :CONNECT.
The :r is used to specify an input file to be executed (in our case query.sql) and the :CONNECT is used to connect to a SQL Server instance.
Notice the GO batch separator after the :r command, without it the script won’t work.
Of course the servers listed are servers in my test environment, so you would need to replace them with your server and save in a file called serv.sql.
Creating Report Using Linux and Bash
Now that we have the disk space information from all our servers as HTML table rows we must include this into an HTML document. For this purpose I created the following bash script.
#!/bin/bash header="<HTML> <BODY> <h1 align="center">Disk Space Report</h1> <TABLE> <TR> <TH bgcolor="#4f81bd">Server</TH> <TH bgcolor="#4f81bd">Database</TH> <TH bgcolor="#4f81bd">File Name</TH> <TH bgcolor="#4f81bd">Type</TH> <TH bgcolor="#4f81bd">Path</TH> <TH bgcolor="#4f81bd">File Size</TH> <TH bgcolor="#4f81bd">File Used Space</TH> <TH bgcolor="#4f81bd">File Free Space</TH> <TH bgcolor="#4f81bd">% Free File Space</TH> <TH bgcolor="#4f81bd">Autogrowth</TH> <TH bgcolor="#4f81bd">volume_mount_point</TH> <TH bgcolor="#4f81bd">Total Volume Size</TH> <TH bgcolor="#4f81bd">Free Space</TH> <TH bgcolor="#4f81bd">% Free</TH> </TR>" bottom="</TABLE></BODY></HTML>" rows=$(sqlcmd -S SQLUX.ATLAS.COM -i serv.sql | grep -vwE "(Sqlcmd|----)" | sort) html=$header$rows$bottom echo $html | mail -s "Database Disk Usage Report" -a "MIME-Version: 1.0" -a "Content-Type: text/html" [email protected]
In the previous script I created two variables named header and bottom to hold the skeleton of the HTML document that will be used as an email template. The header creates the table and the headings for each column and the bottom closes out the HTML document.
One of the advantages of using Linux to create the report is its ability to concatenate commands and redirect outputs to other programs using pipes. In the previous script I took advantage of this as you can see on the following line.
rows=$(sqlcmd -S SQLUX.ATLAS.COM -i serv.sql | grep -vwE "(Sqlcmd|----)" | sort)
What the previous line does is concatenate the three commands and assign the result to the rows variable. This piping redirects the output of the leftmost command as the input for the command at the right of the pipe.
Now I will explain each part of these commands.
sqlcmd -S SQLUX.ATLAS.COM -i serv.sql
This is one we already know. It connects to the server SQLUX.ATLAS.COM using Windows Authentication, executes the serv.sql script and exits after displaying the results in the console.
grep -vwE "(Sqlcmd|----)"
The grep command searches the input data for lines containing a match to a given pattern list. When it finds a match in a line, it copies the line to standard output. The arguments –vwE are to invert the matching criteria. In this case I am asking for lines not starting with either the word Sqlcmd or dashes.
sort
As you may guess, this command sorts the input data and prints it back to the screen (in our script instead of printing to the screen we are assigning the output to the rows variable).
The last line of the bash script is to send the report by email.
echo $html | mail -s "Database Disk Usage Report" -a "MIME-Version: 1.0" -a "Content-Type: text/html" [email protected]
The echo command is used to display the value of a variable to the console, but as you may guess, the pipe redirects its output to the mail command.
On the mail command we pass the subject with the –s argument, and we use the –a argument to append information to the email header. In this case we are giving instructions to the email client to render the email content as HTML.
Final Report Output
The following screen capture shows what the delivered report looks like in my email.
Creating Report Using PowerShell
For those of you who don’t have a Linux system there is also a way to achieve the same goals by using PowerShell. The script logic is the same; we only need to change the way we declare variables and of course the commands.
$header = "<HTML>
<BODY>
<h1 align=""center"">Disk Space Report</h1>
<TABLE>
<TR>
<TH bgcolor=""#4f81bd"">Server</TH>
<TH bgcolor=""#4f81bd"">Database</TH>
<TH bgcolor=""#4f81bd"">File Name</TH>
<TH bgcolor=""#4f81bd"">Type</TH>
<TH bgcolor=""#4f81bd"">Path</TH>
<TH bgcolor=""#4f81bd"">File Size</TH>
<TH bgcolor=""#4f81bd"">File Used Space</TH>
<TH bgcolor=""#4f81bd"">File Free Space</TH>
<TH bgcolor=""#4f81bd"">% Free File Space</TH>
<TH bgcolor=""#4f81bd"">Autogrowth</TH>
<TH bgcolor=""#4f81bd"">volume_mount_point</TH>
<TH bgcolor=""#4f81bd"">Total Volume Size</TH>
<TH bgcolor=""#4f81bd"">Free Space</TH>
<TH bgcolor=""#4f81bd"">% Free</TH>
</TR>"
$bottom="</TABLE></BODY></HTML>"
$var= sqlcmd -S SQLUX.ATLAS.COM -i serv.sql
$rows = echo $var | Select-String -Pattern "^Sqlcmd*", "^----*" -NotMatch | Sort
$secpasswd = ConvertTo-SecureString "YourSMTPPassword" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("UserName", $secpasswd)
Send-MailMessage -From "[email protected]" -Subject "Report" -To "[email protected]" -Body " $header $rows $bottom" -BodyAsHtml -SmtpServer "your.smtp.server" -Credential $mycreds
Next Steps
- Read my previous tip for an introduction to sqlcmd utility: Introduction to SQL Server's sqlcmd utility.
- Also read this other tip about executing script files with sqlcmd: Execute SQL Server Script Files with the sqlcmd Utility.
- In my previous tip Using SQL Server sqlcmd scripting variables, I gave an introduction to the sqlcmd commands like :CONNECT and :r. You should consider reading this tip too.
- For more tips about SQL Server on Linux visit the SQL Server on Linux Tips Category.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.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-09-13
