Script to monitor TransactionLogFileUsage and trigger backups automatically once logfile usage reaches to 40%

  

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

SELECT *
FROM #MonitorTransactionLogFileUsage

/* declare variables */
DECLARE @variable SYSNAME
	,@jobname SYSNAME

DECLARE cursor_name CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT DatabaseName
FROM #MonitorTransactionLogFileUsage
WHERE LogSpaceUsedInPercentage >= 40

OPEN cursor_name

FETCH NEXT
FROM cursor_name
INTO @variable

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @jobname = N'LSBackup_' + @variable

	IF EXISTS (
			SELECT name
			FROM msdb.dbo.sysjobs
			WHERE name = @jobname
			)
	BEGIN
		IF NOT EXISTS (
				SELECT 1
				FROM msdb.dbo.sysjobs_view job
				INNER JOIN msdb.dbo.sysjobactivity activity  

ON job.job_id = activity.job_id
WHERE activity.run_requested_date IS NOT NULL
		AND activity.stop_execution_date IS NULL
		AND job.name = @jobname
				)
		BEGIN
			EXEC msdb.dbo.sp_start_job @job_name = @jobname
		END
		ELSE
		BEGIN
			PRINT 'Job ''' + @jobname + ''' is already started ';
		END
	END

	FETCH NEXT
	FROM cursor_name
	INTO @variable
END

CLOSE cursor_name

DEALLOCATE cursor_name

DROP TABLE #MonitorTransactionLogFileUsage