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

check database status and send email

 USE [master]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER  PROC [dbo].[Database_Status]

AS

BEGIN 

--send email when database changes status--run every 1 min as a job 

DECLARE @state VARCHAR(10)

DECLARE @Database_Name VARCHAR(100)

DECLARE @email VARCHAR(100)

SET @state = ''

SET @email = ''

DECLARE db_cursor CURSOR FOR  

SELECT name 

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('pportal')

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @Database_Name   

WHILE @@FETCH_STATUS = 0   

BEGIN 

SELECT @state = 

CASE 

WHEN a.state = 0 THEN 'Online'

WHEN a.state = 1 THEN 'Restoring'

WHEN a.state = 2 THEN 'Recovery_Pending'

WHEN a.state = 4 THEN 'Suspect'

WHEN a.state = 5 THEN 'Emergency'

WHEN a.state = 6 THEN 'Offline'

WHEN a.state = 7 THEN 'Copying - SQL Azure'

WHEN a.state = 10 THEN 'Ofline_Secondary - SQL Azure'

END

FROM sys.databases a WHERE name = @Database_Name

IF @state <> 'Online'

BEGIN

SET @email = 'The database ' + @Database_Name + ' is currently : ' + @state

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

    @recipients = 'kumardba99@gmail.com',

    @body = @email,

    @subject = 'Database Status' ;

END

  FETCH NEXT FROM db_cursor INTO @Database_Name   

END 

CLOSE db_cursor   

DEALLOCATE db_cursor

END