Auto Log Backup

IF OBJECT_ID('tempdb.dbo.#TmpLOGSPACE', 'U') IS NOT NULL
  DROP TABLE #TmpLOGSPACE;
IF OBJECT_ID('tempdb.dbo.#TmpLOG', 'U') IS NOT NULL
  DROP TABLE #TmpLOG;
create table #TmpLOGSPACE(
  DatabaseName varchar(100),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))

create table #TmpLOG(
  DatabaseName varchar(100),
  LOG_REUSE varchar(30),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))


insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)
EXEC('DBCC SQLPERF(LOGSPACE);')

insert #TmpLOG(DatabaseName, LOG_REUSE, LOGSIZE_MB, LOGSPACE_USED)
(select a.name, a.log_reuse_wait_desc, b.LOGSIZE_MB, b.LOGSPACE_USED
from sys.databases a
inner join #TmpLOGSPACE b
on a.name = b.DatabaseName)

DROP TABLE #TmpLOGSPACE;
DECLARE db_cursor CURSOR FOR
SELECT DatabaseName from #TmpLOG
where (LOGSPACE_USED>50)
AND (DatabaseName not in ('master','tempdb','model','msdb') )
AND LOG_REUSE = 'LOG_BACKUP';
DECLARE @now nvarchar(max) = (select replace(convert(varchar(8), getdate(), 112)+convert(varchar(8), getdate(), 114), ':',''));
declare @LogicalName nvarchar(100) = DB_NAME() + '_TransactionLogBackup_' + @now;
declare @BackupPath nvarchar(200) = 'D:\DBA_Work_Files\BackUp_root\';
declare @newPath nvarchar(200) = ''''+ @BackupPath + @LogicalName + '.trn'+'''';
DECLARE @sqlStr varchar(max), @dbname varchar(50)

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN

       --Do stuff with scalar values
SET @LogicalName = ''''+ DB_NAME() + '_TransactionLogBackup_' + @now + '''';
declare @percent decimal = (Select LOGSPACE_USED from #TmpLOG where DatabaseName = @dbname)

print char(13)+ 'DATABSE: '+ @dbname +char(13)+'Log Used Percentage: '+ convert(varchar(10),@percent)

SET @sqlStr= 'BACKUP LOG '+@dbname+'
TO  DISK = '+@newPath+'
WITH NAME = '+@logicalName+', COMPRESSION'

       --print char(13)+(@sqlStr)
  EXEC (@sqlStr)

       FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
drop table #TmpLOG;

--to make this effective, create a job to execute T-SQL than put it there.