Technical Article

Space monitor on SQL Server

,

Its helpful to monitor over DB Size and we could monitor over DB log file size while its increasing frequently.

SELECT DISTINCT vs.volume_mount_point AS Drive_Name,
  vs.total_bytes/1024/1024/1024 AS Drive_Total_Space,
  vs.available_bytes/1024/1024/1024 AS Drive_Free_Space
into #temp_disc_space
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs

select Drive_Name,(Drive_Free_Space * 100)/Drive_Total_Space as [% Free_space]
into #temp_space_monitor
from #temp_disc_space
order by Drive_Name

if exists(select 1 from #temp_space_monitor where [% Free_space] < 15)
begin
	select * from #temp_space_monitor where [% Free_space] < 15
end 

drop table #temp_disc_space, #temp_space_monitor

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating