Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create SQL Server Disk Space Report for All Servers


By:   |   Read Comments (2)   |   Related Tips: More > Monitoring

Attend a SQL Server Conference for FREE >> click to learn more


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.

Script - Description: This is the basic script that we will use.

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.

HTML Output - Description: We need the results in HTML format.

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.

Sample servers in my test environment

Also, I think that maybe a more graphical representation of the previous script can help even more.

Graphical representation of the script

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"  your@email.com
  

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" your@email.com
  

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.

Final Disk Space Usage Report - Description: This is how the report looks like.
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 "sender@email.com" -Subject "Report" -To "your@email.com" -Body " $header  $rows  $bottom" -BodyAsHtml -SmtpServer "your.smtp.server" -Credential $mycreds
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

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     



Monday, September 18, 2017 - 10:24:36 PM - Daniel Farina Back To Top

Hi Ian!

If what you want is only the volume stats you are ok.

 

Since you asked about highlighting on a threshold I corrected my script to mark at the final report with red rows with less than 10 percent volume space available. Take a look!

Thanks for reading!

 

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' +

CASE WHEN CAST (REPLACE ([% Free File Space], '%', '') AS DECIMAL (15,2) ) < 10 THEN ' bgcolor="#FF0000"' 

ELSE ''

 END 

 

+'>' +

'<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

 


Monday, September 18, 2017 - 4:45:17 PM - Ian Miller Back To Top

Hi Daniel.  Thanks for the script.  I often only need to know how much space is used or free at the volume level.  So, I believe the following SELECT statement works for that purpose - correct me if I'm worng.  You can then do things with the HTML to hilight volumes with less than [your-level-of-comfort] space free etc...

SELECT DISTINCT [volume_mount_point] AS Volume,
   [Total Volume Size],
   [Free Space],
   [% Free]
 FROM #SpaceUsed
 ORDER BY [volume_mount_point];

 

Thanks again


Learn more about SQL Server tools