Surya Rajendra Surya Rajendra - 2 months ago 12
SQL Question

Low disk space alert by percentage

I have a query which gives Server drives space, if it hits less than 25 GB. But I need on percentage Basis like if it hits less than 10% then it should alert us. I require this as we are in discussions about buying monitoring tools.

Here is the query:

Create Procedure dbo.DiskSpaceAlert_Surya

As
Begin

create table #DriveSpaceLeft (Drive varchar(10),
[MB Free] bigint)
insert #DriveSpaceLeft (Drive, [MB Free])
EXEC master.dbo.xp_fixeddrives;

create table DrivesWithIssue (Drive varchar(10),
[MB Free] bigint )

insert into DrivesWithIssue
select Drive, [MB Free] from #DriveSpaceLeft
where [MB Free] < 25000

drop table #DriveSpaceLeft

declare @cnt int
select @cnt=COUNT(1) from DrivesWithIssue
if (@cnt > 0)
begin

declare @strsubject varchar(100)
select @strsubject='Check drive space on ' + @@SERVERNAME

declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H1>Drives with less that 25 GB Free - ' + @@SERVERNAME + '</H1>' +
N'<table border="1">' +
N'<tr><th>Drive</th>' +
N'<th>MB Free</th></tr>' +
CAST ( ( SELECT td = [Drive], '',
td = [MB Free]
FROM DrivesWithIssue
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
--@from_address='test@test.com',
@recipients='',
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML' ,
@profile_name='Databasemail'
end

drop table DrivesWithIssue
End


And the result is

enter image description here

I need another column as percentage Free.

Please feel free to share if you have any other scripts to fulfill this requirement.

Answer

I have found a way to solve the same...

SP to get an alert if drive space is < 10

USE [MYDB]
GO

/****** Object:  StoredProcedure [dbo].[DiskSpaceAlert_Surya]    Script Date: 3/24/2015 11:49:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create Procedure [dbo].[DiskSpaceAlert_Surya]

As
Begin
declare @cnt int 

create table #DriveSpaceLeft (Drive varchar(10), [total_space] bigint,
                              [MB_Free] bigint, [percentage_free] decimal(12,2))
insert #DriveSpaceLeft (Drive, [total_space],[MB_Free],[percentage_free])
     SELECT DISTINCT 
	--S.TOTAL_BYTES,s.available_bytes,s.logical_volume_name,*,
			s.volume_mount_point [Drive],
			CAST(s.TOTAL_BYTES / 1048576 as decimal(12,2)) [Total Space MBs],
			CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs],
			(CAST(s.available_bytes / 1048576 as decimal(12,2)) / 
			CAST(s.TOTAL_BYTES / 1048576 as decimal(12,2)) * 100) [Percentage]
		FROM 
			sys.master_files f
			CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s;
	 SELECT @cnt=COUNT(*) FROM #DriveSpaceLeft  WHERE percentage_free < 50
 
if (@cnt > 0)
begin
   	declare @strsubject varchar(100)
	select @strsubject='Check drive space on ' + @@SERVERNAME

	declare @tableHTML  nvarchar(max);
	set @tableHTML =
	    N'<H1>Drives with less than 10% Free  - ' + @@SERVERNAME + '</H1>' +
		N'<table border="1">' +
		N'<tr><th>Drive</th>' +
		N'<th>Total space</th>'+
        N'<th>MB Free</th>'+
	    N'<th>Percentage (%) Free</th></tr>' +
	    CAST ( ( SELECT td = [Drive], '',
                		td = [total_space], '',
						td = [MB_Free], '      ',
						td = [percentage_free]
						FROM #DriveSpaceLeft WHERE percentage_free < 50
				  FOR XML PATH('tr'), TYPE 
		) AS NVARCHAR(MAX) ) +
		N'</table>' ;

	EXEC msdb.dbo.sp_send_dbmail
	@recipients='con-balususr@mail.com;',
	@subject = @strsubject,
	@body = @tableHTML,
	@body_format = 'HTML' ,
	@profile_name='Databasemail'
	
end
DROP TABLE #DriveSpaceLeft
End

GO

And the Result is (I have tested for below 50%)

enter image description here

Thanks all for your valuable Time....