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
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
