T-SQL Scripts to generate the HTML-formatted database storage report

This article explains the T-SQL scripts that can be used to manage and monitor the disk space on the database server. The T-SQL script contains queries to populate the following details.

List of drives, size of disk, and percentage available space
Database files and their location.
List of the database with other configuration parameters.

Moreover, we will create an HTML-formatted email that contains details of the above items.

Get a list of drives, size of disk, and percentage available space

We can use the xp_fixeddrives stored procedure to populate the details of the server's disk drives. This procedure only provides the available space on the server for each drive. You can see this below..

USE master
GO
exec xp_fixeddrives

Query output

We can also use a dynamic management function, named sys.dm_os_volume_stats, to populate the disk space. You can read more about it here.

To populate the details, run the following query statement in SQL Server management studio or dbForge Studio for SQL Server:

SELECT DISTINCT
volume_mount_point [Mount Point],
file_system_type [File System],
logical_volume_name as [Logical Drive],
CONVERT(Numeric(10,2),total_bytes/1048576/1024) AS [Total Size],
Convert(Numeric(10,2),available_bytes/1048576/1024) AS [Available Space],
CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

You can see the query output below.

Alternatively, you can use Get-WmiObject or commandlet to populate the disk information of the local computer. The commands are the following:

Get-WmiObject -Class win32_logicaldisk | Format-Table DeviceId, MediaType, @{n="Total Size";e={[math]::Round($_.Size/10MB,2)}},@{n="Available Space";e={[math]::Round($_.FreeSpace/10MB,2)}}

Using this PowerShell will return the following information:

In this article, we use sys.dm_os_volume_stats to populate the storage details on the server. You can read more about it in this article. The HTML code to create a table with disk information is following:

DECLARE @HTMLString nvarchar(max)
SET @HTMLString=
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>Disk space summery</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Volume</font></td>
         <td bgColor="#99CC33">Volume Name</td>
         <td bgcolor="#99CC33" height="15">Total Space (GB)</font></b></td>
         <td bgcolor="#99CC33">Available Space (GB)</td>
         <td bgcolor="#99CC33">Available Space In Percent</td>
     </tr>'
+CAST((SELECT distinct
    td =  volume_mount_point ,' ' ,
   td= logical_volume_name ,' ' ,
   td = Convert(VARCHAR,CONVERT(Numeric(10,2),total_bytes/1048576/1024)),' ' ,
   td = Convert(VARCHAR,Convert(Numeric(10,2),available_bytes/1048576/1024)),' ' ,
   td = CONVERT(VARCHAR,CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100),' '
FROM
 sys.master_files masterfiles
CROSS APPLY sys.dm_os_volume_stats(masterfiles.database_id, masterfiles.FILE_ID)volume FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'

The HTML webpage looks like the following image:

Database files with their location

We can use sys.master_files dynamic management view to show the details of the database file types, their size, growth, and physical location. You can read this article to learn more about the sys.master_files.

The SQL Code to generate the HTML table is following:

DECLARE @HTMLString nvarchar(max)
SET @HTMLString=
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>Database File location summary</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Database Name</font></td>
         <td bgColor="#99CC33">Database file Type</td>
         <td bgcolor="#99CC33" height="15">File name</font></b></td>
         <td bgcolor="#99CC33">File Location</td>
         <td bgcolor="#99CC33">Database file status</td>
         <td bgcolor="#99CC33">Initial Size</td>
         <td bgcolor="#99CC33">Maximum Size</td>
     </tr>'
+CAST((SELECT distinct
    td =  DB_NAME(database_id) ,' ' ,
   td= type_desc ,' ' ,
   td = name,' ' ,
   td = physical_name,' ' ,
   td = state_desc,' ' ,
   td=SIZE,' ',
   td=max_size,' '
FROM sys.master_files masterfiles WHERE DB_NAME(database_id)
NOT IN
('master','msdb','tempdb','model') FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'
SELECT @HTMLString

The HTML formatted output looks like the following image:

Now, let us see how we can populate the list of databases with their size. We can populate the list of all databases with their size by querying any of the following:

Execute sp_databases
Joining sys.databases and sys.master files.

The example of sp_databases is here:

USE master
GO
EXEC sys.sp_databases

The output is shown below:

We can join the sys.databases and sys.master_files dynamic management views. Both DMVs have database_id columns, which we can use to join them. You can read more about the sys.databases from this article.

In this demo, we are using dynamic management views. The SQL Code to generate the HTML table that contains the list of databases and their size is following:

DECLARE @HTMLString nvarchar(max)
SET @HTMLString=
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>Database Size and Other details</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Database Name</font></td>
         <td bgColor="#99CC33">Database Status</td>
         <td bgcolor="#99CC33" height="15">Database create date</font></b></td>
         <td bgcolor="#99CC33">Compatibility Level</td>
         <td bgcolor="#99CC33">Database Recovery Model</td>
         <td bgcolor="#99CC33">Delayed Durability</td>
         <td bgcolor="#99CC33">Containtment</td>
         <td bgcolor="#99CC33">Database Size</td>
     </tr>'
+CAST((SELECT distinct
    td =  dbs.NAME ,' ' ,
   td=  dbs.state_desc ,' ' ,
   td = CONVERT(DATETIME, dbs.create_date),' ' ,
   td = dbs.compatibility_level,' ' ,
   td = dbs.recovery_model_desc,' ' ,
   td=dbs.delayed_durability_desc,' ',
   td= dbs.containment_desc,' ',
   td= CONVERT(NVARCHAR,(Sum(Cast(masterfiles.size AS BIGINT)) * 8 / 1024)),' '
FROM   sys.master_files masterfiles
   INNER JOIN sys.databases dbs
           ON dbs.database_id = masterfiles.database_id
WHERE  dbs.database_id > 4 -- Skip system databases
GROUP  BY dbs.NAME,
      dbs.NAME,
      dbs.state_desc,
      dbs.compatibility_level,
      dbs.create_date,
      dbs.recovery_model_desc,
      dbs.delayed_durability_desc,
      dbs.containment_desc,
      dbs.default_language_name,
      dbs.default_fulltext_language_name
ORDER  BY dbs.NAME  FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'

The HTML formatted output looks like the following image:

Now, we want to combine all the details in one email report. To do that, In the script, I have declared a variable, named @DatabaseHTMLString. The data type of the variable is nvarchar(max). We will store the query output in the HTML formatted table in @DatabaseHTMLString. We use string concatenation to wrap the results from our stored procedure with HTML code.

The final script to show the disk space, database file location, and database size is following:

DECLARE @HTMLString_1 nvarchar(max) 
DECLARE @HTMLString_2 nvarchar(max) 
DECLARE @DatabaseHTMLString nvarchar(max)  
SET @HTMLString_1=
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>Disk space summery</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Volume</font></td>
         <td bgColor="#99CC33">Volume Name</td>
         <td bgcolor="#99CC33" height="15">Total Space (GB)</font></b></td>
         <td bgcolor="#99CC33">Available Space (GB)</td>
         <td bgcolor="#99CC33">Available Space In Percent</td>
     </tr>'
+CAST((SELECT distinct
    td =  volume_mount_point ,' ' ,
   td= logical_volume_name ,' ' ,
   td = Convert(VARCHAR,CONVERT(Numeric(10,2),total_bytes/1048576/1024)),' ' ,
   td = Convert(VARCHAR,Convert(Numeric(10,2),available_bytes/1048576/1024)),' ' ,
   td = CONVERT(VARCHAR,CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100),' '
FROM
 sys.master_files masterfiles
CROSS APPLY sys.dm_os_volume_stats(masterfiles.database_id, masterfiles.FILE_ID) FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'
 
 
/*Database File Location Details*/SET @HTMLString_2=@HTMLString_1+
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>Database File location summary</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Database Name</font></td>
         <td bgColor="#99CC33">Database file Type</td>
         <td bgcolor="#99CC33" height="15">File name</font></b></td>
         <td bgcolor="#99CC33">File Location</td>
         <td bgcolor="#99CC33">Database file status</td>
         <td bgcolor="#99CC33">Initial Size</td>
         <td bgcolor="#99CC33">Maximum Size</td>
     </tr>'
+CAST((SELECT distinct
    td =  DB_NAME(database_id) ,' ' ,
   td= type_desc ,' ' ,
   td = name,' ' ,
   td = physical_name,' ' ,
   td = state_desc,' ' ,
   td=SIZE,' ',
   td=max_size,' '
FROM sys.master_files masterfiles WHERE DB_NAME(database_id)
NOT IN
('master','msdb','tempdb','model') FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'
/*Database Details*/SET @DatabaseHTMLString=@HTMLString_2+
'<table id="AutoNumber1" borderColor="#111111" border="1">
<P><TR></TR><H2>Database Size and Other details</H2><TR></TR></P>
     <tr>
         <td bgcolor="#99CC33">Database Name</font></td>
         <td bgColor="#99CC33">Database Status</td>
         <td bgcolor="#99CC33" height="15">Database create date</font></b></td>
         <td bgcolor="#99CC33">Compatibility Level</td>
         <td bgcolor="#99CC33">Database Recovery Model</td>
         <td bgcolor="#99CC33">Delayed Durability</td>
         <td bgcolor="#99CC33">Containtment</td>
         <td bgcolor="#99CC33">Database Size</td>
     </tr>'
+CAST((SELECT distinct
    td =  dbs.NAME ,' ' ,
   td=  dbs.state_desc ,' ' ,
   td = CONVERT(DATETIME, dbs.create_date),' ' ,
   td = dbs.compatibility_level,' ' ,
   td = dbs.recovery_model_desc,' ' ,
   td=dbs.delayed_durability_desc,' ',
   td= dbs.containment_desc,' ',
   td= CONVERT(NVARCHAR,(Sum(Cast(masterfiles.size AS BIGINT)) * 8 / 1024)),' '
FROM   sys.master_files masterfiles
   INNER JOIN sys.databases dbs
           ON dbs.database_id = masterfiles.database_id
WHERE  dbs.database_id > 4 -- Skip system databases
GROUP  BY dbs.NAME,
      dbs.NAME,
      dbs.state_desc,
      dbs.compatibility_level,
      dbs.create_date,
      dbs.recovery_model_desc,
      dbs.delayed_durability_desc,
      dbs.containment_desc,
      dbs.default_language_name,
      dbs.default_fulltext_language_name
ORDER  BY dbs.NAME  FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'</table>'

The HTML formatted output looks like the following image:

Using the above code, you can create a stored procedure to send automated emails with storage details using database mail.

Summary

In this article, I have shown a T-SQL Script used to generate a report that shows the disk space, location of database files and databases with their size and other parameters. In my future articles, I will explain how to show the storage details and other key matrix on SSRS dashboard.

T-SQL Scripts to generate the HTML-formatted database storage report

Get a list of drives, size of disk, and percentage available space

Database files with their location

Summary

Rate

Share

Categories

Share

Rate

T-SQL Scripts to generate the HTML-formatted database storage report

Get a list of drives, size of disk, and percentage available space

Database files with their location

Summary

Rate

Share

Categories

Share

Rate

Related content

Different Ways to Populate the List of Tables

How to Find the SQL Server Instance Startup Time

Find Invalid Objects in SQL Server

The Top SQL Server Engine Errors

The Struggles to Keep Up with Growing Systems