USE msdb SET NOCOUNT ON; IF OBJECT_ID('tempdb..#MonitorTransactionLogFileUsage') IS NOT NULL DROP TABLE #MonitorTransactionLogFileUsage CREATE TABLE #MonitorTransactionLogFileUsage ( ID INT IDENTITY(1, 1) ,DatabaseName SYSNAME ,LogSizeInMB DECIMAL(18, 5) ,LogSpaceUsedInPercentage DECIMAL(18, 5) ,[Status] INT ) INSERT INTO #MonitorTransactionLogFileUsage ( DatabaseName ,LogSizeInMB ,LogSpaceUsedInPercentage ,[Status] ) EXEC ('DBCC SQLPERF(LOGSPACE)') IF OBJECT_ID('tempdb..#LogfileGrowth') IS NOT NULL DROP TABLE #LogfileGrowth CREATE TABLE #LogfileGrowth (LogGrowth VARCHAR(4000)) INSERT INTO #LogfileGrowth SELECT 'Database ' + '' + cast(DatabaseName AS VARCHAR(50)) +
' LogSpaceUsedInPercentage has grown to ' +
cast(LogSpaceUsedInPercentage AS VARCHAR(50)) + '' + '% ' + ' ' + '' +
'Database recover model is ' + '' + cast(recovery_model_desc
COLLATE Latin1_General_CI_AS_KS_WS AS VARCHAR(50)) + ' and it is growing due to '
+ cast(log_reuse_wait_desc AS VARCHAR(150)) LogGrowth FROM #MonitorTransactionLogFileUsage a INNER JOIN sys.databases b ON a.DatabaseName = b.name AND a.DatabaseName = 'DB Name you would like to monitor ' WHERE LogSpaceUsedInPercentage > 60 DECLARE @body1 VARCHAR(4000) SET @body1 = ( SELECT TOP 1 LogGrowth FROM #LogfileGrowth ) DECLARE @server_name VARCHAR(100); SELECT @server_name = convert(VARCHAR(100), SERVERPROPERTY('servername')); DECLARE @sub VARCHAR(1000); SELECT @sub = 'Logfile is getting filled on ' + @server_name + ' '; DECLARE @rowcount INT SELECT @rowcount = ( SELECT count(1) FROM #LogfileGrowth HAVING count(1) > 0 ) IF @rowcount > 0 BEGIN --database-notifications@accuratebackground.pagerduty.com EXEC msdb.dbo.sp_send_dbmail @profile_name = 'your db profile name' ,@recipients = ' mention list of recipients you need to send mail' ,@subject = @sub ,@body = @body1 ,@body_format = 'HTML'; END
No comments:
Post a Comment