Shrink LOG files automatically, SQL Server

Use below script to shrink T-LOG files. This script will shrink the log file which is larger than the value of the variable "@LOG_SIZE_LIMIT". You can change it to meet your requirement.

To run this script automatically, create a SQL JOB and place this script in the STEP. That's all.

See Also: SHRINK DATABASE, Check Job Status, Grant Access to SQL Agent, View Job history.

USE master; GO DECLARE @LOG_SIZE_LIMIT int = 10240 -- 10GB declare  DBName cursor for select a.DATABASE_NAME,mf. name AS LogicalFileName ,LOG_SIZE_MB from( SELECT DATABASE_NAME = DB_NAME(s.database_id)     , LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(10,2))     , ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(10,2))     , TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2)) FROM sys.databases s JOIN sys.master_files m ON s.database_id = m.database_id where s.database_id > 4 and s.state_desc= 'ONLINE' and s.recovery_model_desc = 'SIMPLE' GROUP BY s.database_id,s.name ) a inner join sys.master_files AS mf on DB_NAME(mf.database_id) = a.DATABASE_NAME and mf.type_desc = 'LOG' where LOG_SIZE_MB > @LOG_SIZE_LIMIT ORDER BY LOG_SIZE_MB desc declare @DBNAme varchar(1000), @LogicalFileName varchar(1000), @LOG_SIZE_MB int, @SQLExec varchar(4000) open DBName fetch next from DBName into @DBNAme,@LogicalFileName,@LOG_SIZE_MB WHILE @@FETCH_STATUS=0 BEGIN SET @SQLExec= 'use '+@DBNAme+'; DBCC shrinkfile ('+@LogicalFileName+')' print @SQLExec execute (@SQLExec) fetch next from DBName into @DBNAme,@LogicalFileName,@LOG_SIZE_MB END close DBName deallocate DBName

Thanks.