How to automatically reduce the transaction log?

Question

SQL Server

Hello colleagues.
The number of databases on both MSSQL(2012) servers is growing rapidly and more and more often I have to be distracted by pruning logs.
For all databases, I have set the maximum log size, since thin allocation of disk capacities on virtuals is used (historically, the administrator who did this is no longer poured)
Wrote a terrible script (did not know how to call the procedure giving it variables), but run on I'm afraid of combat servers

Declare @name varchar(100)
declare @qu as varchar(1200)
declare icur cursor fast_forward for

SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
--and recovery_model_desc = 'FULL'

open icur
 fetch next from icur into @name
 While @@Fetch_Status = 0 

Begin
  Set @qu='use [' + @name + '] Declare @logname varchar(64), @size int'
  Set @[email protected] + ' Set @logname = (SELECT [name] FROM [sys].[database_files]  where type_desc=''LOG'')'
  Set @[email protected] + ' Set @size = (SELECT max_size FROM [sys].[database_files]  where type_desc=''LOG'') * 0.7/128'
  Set @[email protected] +  ' ALTER DATABASE [' + @name + ']  SET RECOVERY SIMPLE DBCC SHRINKFILE (@logname, @size)'
  Set @[email protected] + ' ALTER DATABASE [' + @name + ']  SET RECOVERY FULL'
  Exec (@qu) 
  Set @qu = '' 
  fetch next from icur into @name
END
close icur
deallocate icur

I know that according to the Melkomyagky instructions, when creating a backup, completed transactions are deleted from the LOG, and then SQL must write new transactions to the freed space, but it still grows and I constantly have to reduce the size with the DBCC SHRINKFILE command.
I have FULL Recovery for databases, so the logs are backed up complete with the tables themselves, and I don’t need to backup the log separately, and there’s nowhere. Autogrowth is enabled, in small increments.
So colleagues, how do you solve this issue with greatly growing logs? And how right? Maybe there is some setting or recommended algorithm?

Answer 1

Using the Maintenance Plan Wizard .
Drawing up SQL maintenance plans for the needs of 1C .