When it comes to monitoring and managing disk space we may have some graphic tools to help us complete these tasks rapidly, nevertheless, not everybody can afford to pay the tools, and most of them do not give some details we may need on the spur of the moment. For example, we may have the need to check how much disk space is occupied by database files exclusively so that a wise decision can be made to ensure we are using disk space properly of a database server. We may need to see a report something like this.
In the report we can appreciate that all partition disks are displayed, each of them has information related to the space used in total (db_file_size_on_disk_db) by all database files (data & log) and it also includes the equivalent in percentage (db_file_size_on_disk_pct). Thus, we will be able to know whether or not the disk space is being using properly. Furthermore, disk space capacity and disk space available in gigabytes and percentage can also seen (disk_capacity_gb, disk_space_available_gb, disk_space_available_pct).
Now it is high time to show the code to get that report containing that information:
CREATE PROC [dbo].[sp_DBA_mon_db_files_size_on_disk] (@volume_mount_point char(1)=NULL)
/*WITH ENCRYPTION*/
AS
begin
if (@volume_mount_point is not null )
begin
SELECT vs.volume_mount_point,
vs.logical_volume_name,
DB_NAME(f.database_id) database_name, f.type_desc,
vs.total_bytes/1024/1024/1024.0 AS [disk_capacity_gb],
cast( f.[size] / 128 /1024.0 as decimal(10,2)) AS [db_file_size_on_disk_gb],
cast( ( f.[size]*100/ 128 /1024.0 ) / (vs.total_bytes/1024/1024/1024.0 ) as DECIMAL(10,2) ) AS [db_file_size_on_disk_pct],
cast(vs.available_bytes/1024/1024/1024.0 as DECIMAL(10,2)) AS [disk_space_available_gb],
cast ( ( vs.available_bytes *100.0 / vs.total_bytes) as DECIMAL(10,2) ) AS [disk_space_available_pct],
f.physical_name,
vs.file_system_type
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
WHERE f.database_id < 32767 and vs.volume_mount_point=@volume_mount_point + ':\'
order by f.[size] desc
end
else
begin
SELECT vs.volume_mount_point,
vs.logical_volume_name,
vs.total_bytes/1024/1024/1024.0 AS [disk_capacity_gb],
cast(SUM( f.[size] / 128 /1024.0) as decimal(10,2)) AS [db_file_size_on_disk_gb],
cast( (SUM( f.[size]/ 128 /1024.0) *100) / (vs.total_bytes/1024/1024/1024.0 ) as DECIMAL(10,2) ) AS [db_file_size_on_disk_pct],
cast(vs.available_bytes/1024/1024/1024.0 as DECIMAL(10,2)) AS [disk_space_available_gb],
cast ( ( vs.available_bytes *100.0 / vs.total_bytes) as DECIMAL(10,2) ) AS [disk_space_available_pct],
vs.file_system_type
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
WHERE f.database_id < 32767
group by vs.volume_mount_point,vs.total_bytes , vs.logical_volume_name, vs.total_bytes ,vs.file_system_type , vs.available_bytes
end
end
The report could receive the letter of the partition disk as a parameter, and also it can be omitted. As you can see in the definition of the stored procedure, the default value for @volume_mount_point is NULL which means, according to the logic of it, that general information will be displayed, that is the report in the graphic, otherwise, giving the letter of a partition will allow you to get detail information for every database file located in that disk partition. That's is all for now. I hope it is useful and practical for you. Let me know any remarks you may have. Thanks for reading. Stay tuned.
