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