send space alert

 USE [master]

GO

/****** Object:  StoredProcedure [dbo].[SendSpaceAlert]    Script Date: 7/29/2021 12:36:59 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

ALTER  PROC [dbo].[SendSpaceAlert]

AS

BEGIN

SET NOCOUNT ON

Declare @Drives Varchar(20)

DECLARE @Spaces Varchar(50)

DECLARE @availableSpace FLOAT

DECLARE @alertMessage VARCHAR(4000)

DECLARE @RecipientsList  VARCHAR(4000);

CREATE TABLE #tbldiskSpace

(

driveName VARCHAR(3),

freeSpace FLOAT

)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives

SELECT @Drives = Isnull(@Drives, '') + drivename + ',' 

FROM   #tbldiskspace 

WHERE  freespace  < 20000

SELECT @Spaces = Isnull(@Spaces, '') 

                 + CONVERT(VARCHAR(10), Round((freespace)/1024, 1)) 

                 + ' GB, ' 

FROM   #tbldiskspace 

WHERE  freespace  < 20000

ORDER  BY drivename 

IF LEN(@Drives) > 1 

BEGIN 

    SET @alertMessage = 'Disk space is critical on ' + @@SERVERNAME 

                        + '. Free Space Available on ' + @Drives 

                        + 'Drive/s is ' + CAST(@Spaces AS VARCHAR) 

    SET @RecipientsList =N'abc@abccom'

    EXEC msdb.dbo.Sp_send_dbmail 

      @profile_name = 'Default', 

      @recipients =@RecipientsList,

      @body = @alertMessage, 

      @importance = 'High', 

      @subject = 'LOW SPACE ON SERVER'; 

END 

DROP TABLE #tbldiskSpace

END