Avoid Transaction Logs volume from filling

USE MASTER 

GO 

CREATE TABLE #TMPFIXEDDRIVES ( 
  DRIVE  CHAR(1), 
  MBFREE INT) 

INSERT INTO #TMPFIXEDDRIVES 
EXEC xp_FIXEDDRIVES 

CREATE TABLE #TMPSPACEUSED ( 
  DBNAME    VARCHAR(1000), 
  FILENME   VARCHAR(1000) 
 ) 

INSERT INTO #TMPSPACEUSED 
select sys.databases.name as DBNAME, sys.master_files.name AS FLNAME  from sys.databases
JOIN sys.master_files 
on sys.databases.database_id = sys.master_files.database_id
where sys.databases.database_id > 4 and sys.master_files.type = 1

declare @drive varchar(10)
declare @diskfree numeric
declare @databasename varchar(256)
declare @filename varchar(1000)
declare @filetype varchar(10)
declare @filesize numeric
declare @pname varchar(256)
declare @alter nvarchar(256)
DECLARE @path VARCHAR(256)
DECLARE @fileName2 VARCHAR(256)  
DECLARE @fileDate VARCHAR(20)
declare @sqlcmd nvarchar(256)
declare @Querymodel as nvarchar(256)

SET @path = 'C:\BackupLogs\'   -- Path for transaction log backups
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 


declare xQuery cursor FOR

SELECT   C.DRIVE, 
         C.MBFREE AS DISKSPACEFREE, 
         A.NAME AS DATABASENAME, 
         B.NAME AS FILENAME, 
         B.TYPE AS FILETYPE,
 B.size AS FILESIZE, 
         B.PHYSICAL_NAME 
FROM     SYS.DATABASES A 
         JOIN SYS.MASTER_FILES B 
           ON A.DATABASE_ID = B.DATABASE_ID 
         JOIN #TMPFIXEDDRIVES C 
           ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE 
         JOIN #TMPSPACEUSED D 
           ON A.NAME = D.DBNAME 
         AND B.NAME = D.FILENME 
ORDER BY DISKSPACEFREE 

 
OPEN xQuery
Fetch next from xQuery Into @drive, @diskfree, @databasename, @filename, @filetype, @filesize, @pname

While @@FETCH_STATUS = 0

BEGIN

     
set @Querymodel = (select recovery_model_desc from sys.databases where name= @databasename and database_id > 4)

if @Querymodel = 'FULL' 
 
BEGIN
   Print 'Database ' + @databasename + ' in FULL mode'
IF @diskfree < 3000 -- Transaction Logs volume free space check, 3GB in the case
  BEGIN 
  Print 'Transaction Log Volume free space below 3GB , starting Backup for ' + @databasename
SET @fileName2 = @path + @databasename + '_' + @fileDate + '_log.BAK'  
BACKUP LOG @databasename TO DISK = @fileName2

if @@error <> 3013

BEGIN
Print 'Backup terminated successfully, starting log shrink for ' + @databasename
set @alter ='Alter database ' + @databasename + ' SET recovery SIMPLE'
exec sp_executesql @alter

set @sqlcmd = ('USE [' + @databasename + ']; ')
set @sqlcmd = @sqlcmd + 'DBCC SHRINKFILE (' + @filename + ',1)' 
exec (@sqlcmd)

set @alter ='Alter database ' + @databasename + ' SET recovery FULL'
exec sp_executesql @alter
END
ELSE
Print 'Transaction Log Backup failed, please make a full backup for ' + @databasename
END
ELSE
Print 'Transaction Log Volume with enough free space'

  END

 Fetch next from xQuery Into @drive, @diskfree, @databasename, @filename, @filetype, @filesize, @pname
 
END

Close xQuery
Deallocate xQuery  

          
DROP TABLE #TMPFIXEDDRIVES 

DROP TABLE #TMPSPACEUSED