SQL SERVER DBA, Linux and Azure: DB_Growth status Report Alert

DB_Growth status Report Alert

Hi Team,

Please find below mentioned script to get an alert DB_Growth status Report via email.

Once executed the following script and then run exec [DB_Growth] proc to get an alert


 USE [master]

GO


/****** Object:  StoredProcedure [dbo].[DB_Growth]    Script Date: 10/28/2021 5:26:50 AM ******/

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE Proc [dbo].[DB_Growth]  

  

As  

  

Begin  

  

DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)  

  

DECLARE @xml NVARCHAR(MAX)  

  

DECLARE @body NVARCHAR(MAX)  

  

  

  

DECLARE @DBSpaceInfo TABLE     

  

( ServerName VARCHAR(100),     

  

  DatabaseName VARCHAR(100),   

  

  PhysicalFileName NVARCHAR(520),     

  

  FileSizeMB DECIMAL(10,2),     

  

  SpaceUsedMB DECIMAL(10,2),    

  

  FreeSpaceMB DECIMAL(10,2),   

  

  FreeSpacePct varchar(8),  

  

  Capture_Date Datetime   

  

)   

  

  

  

 SELECT @command = 'Use [' + '?' + '] SELECT     

  

 @@servername as ServerName,     

  

 ' + '''' + '?' + '''' + ' AS DatabaseName   , name   

  

, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB   

  

, convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB   

  

, convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB,   

  

CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,  

  

Getdate() As Capture_Date  

  

from sys.database_files a'   

  

  

  

INSERT INTO @DBSpaceInfo   

  

EXEC sp_MSForEachDB @command     

  

  

  

  

  

SET @xml = CAST(( SELECT DB.ServerName As 'td',' ',DB.DatabaseName AS 'td' ,' ',DB.PhysicalFileName as 'td' ,' ',DB.FileSizeMB as 'td',' ',Db.SpaceUsedMB as 'td',  

  

' ',DB.FreeSpaceMB as 'td' ,' ',DB.FreeSpacePct as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @DBSpaceInfo  DB  

  

 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  

  

  

  

  

  

SET @body ='<html><body><H3>Database Growth status </H3>  

  

<table border = 1>   

  

<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th>  

  

<td bgcolor=#F0E68C><b>FreeSpace(%)<b> </td> <th>Capture_Date</th></tr>'      

  

--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'      

  

   

  

SET @body = @body + @xml +'</table></body></html>'  

  

  

  

Set @cmd= @@SERVERNAME + ': Database Growth status '  

  

  

  

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile  

  

  

  

EXEC msdb.dbo.sp_send_dbmail  

  

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile   

  

@body = @body,  

  

@body_format ='HTML',  

  

@recipients = 'sqldbanow@gmail.com', -- replace with your email address  

  

@subject = @cmd  

  

End  

 

GO






No comments:

Post a Comment