Error: 9002, Severity: 17 log_reuse_wait_desc = ‘log_backup’ transaction log full

Shrink log file for log_reuse_wait_desc = ‘log_backup’, there are cases we are getting often that transaction log full due do lack of log_backup or sometimes in simple mode the log is full for larger high IOPS transaction.

Take a look on this –> https://sqlserverblogforum.com/dba/database-transaction-log-file-full-in-the-full-recovery-model/

Here is the script to shrink transaction log file. schedule it on agent for every hour.

/*

-- create database DBA_DB
--drop table tbl_log_shrink_temp_load
use DBA_DB 
go

create table [dbo].[tbl_log_shrink_temp_load](
  [db] [nvarchar](128) null,
  [size_mb] [int] null,
  [log_reuse_wait_desc] [nvarchar](60) null,
  [name] [sysname] not null,
  [recovery_model_desc] [nvarchar](60) null
) 
go

--drop table tbl_log_shrink_history
create table [dbo].[tbl_log_shrink_history](
  [db] [nvarchar](128) null,
  [size_mb] [int] null,
  [log_reuse_wait_desc] [nvarchar](60) null,
  [name] [sysname] not null,
  [recovery_model_desc] [nvarchar](60) null,
  [date] [datetime] null
) on [primary]

go

alter table [dbo].[tbl_log_shrink_history] add  default (getdate()) for [date]
go

*/

-- drop proc usp_shrink_log_file
-- select * from tbl_log_shrink_history order by date desc


use DBA_DB 
go

alter proc usp_shrink_log_file
(@backup_location varchar(100))
as
/*
Summary: Shrink the log file log_reuse_wait_desc = 'log_backup' or nothing and recovery_model_desc <>'simple'
and the size greater than 5GB, you can change the threshold
Contact:     muthukkumaran kaliyamoorhty
Description: big log file shrink

changelog:
date         coder                          description
2018-15-june     muthukkumaran kaliyamoorhty     initial


*/

begin

declare @log_wait varchar(50)
declare @db varchar(50)
declare @minrow int
declare @maxrow int
declare @logical_name varchar(100)
declare @sql varchar(1000)
declare @sql1 varchar(1000)
declare @sql2 varchar(1000)
declare @bak_location varchar(1000)
declare @recovery_mode varchar(50)


set @bak_location= @backup_location
--select  @bak_location
truncate table tbl_log_shrink_temp_load

insert into tbl_log_shrink_temp_load

select db_name(f.database_id) as db, f.size / 128 as size_mb,d.log_reuse_wait_desc,f.name,recovery_model_desc 
--into tbl_log_shrink_temp_load
from master.sys.databases d
join master.sys.master_files f
on ( d.database_id = f.database_id )
where f.type_desc = 'log'
and f.database_id not in( 1, 2, 3, 4 )
and f.size / 128 > 5120 -- > 5GB
and d.is_read_only=0
group by db_name(f.database_id),f.size,d.log_reuse_wait_desc,f.name,recovery_model_desc


declare @shrink_log table (
id int  primary key identity, db varchar(100),
log_reuse_wait_desc varchar(100), name varchar (50),recovery_model_desc varchar (50)
) 
 
 insert into @shrink_log
 select db , log_reuse_wait_desc, name, recovery_model_desc  from tbl_log_shrink_temp_load 
 -- where log_reuse_wait_desc = 'nothing'
  --select db , log_reuse_wait_desc   from tbl_log_shrink_temp_load 
  
select @minrow = min(id) from   @shrink_log  --where log_reuse_wait_desc = 'nothing'
select @maxrow  = max(id) from   @shrink_log  --where log_reuse_wait_desc = 'nothing'

select  @log_wait = log_reuse_wait_desc from  @shrink_log  --where log_reuse_wait_desc = 'nothing'

--select @log_wait 

if  (@log_wait='nothing')

begin


     while (@minrow <=@maxrow)
     begin
     select @db=db, @logical_name=name from @shrink_log where id = @minrow and log_reuse_wait_desc = 'nothing'
 
     --print @logical_name
 
    set @sql='exec (''use[' + @db+ '];dbcc shrinkfile(''''' +@logical_name+''''')'')'
    --print (@sql)
    exec (@sql)
 
    set @minrow =@minrow +1 
    end

end


else
--select @log_wait 
    begin

    select @minrow = min(id)from   @shrink_log  where log_reuse_wait_desc = 'log_backup' and recovery_model_desc <>'simple'
    select @maxrow  = max(id) from   @shrink_log  where log_reuse_wait_desc = 'log_backup' and recovery_model_desc <>'simple'


    --select @minrow,@maxrow
 
     while (@minrow <=@maxrow)-- and @recovery_mode<>'simple')
     begin

     select @db=db, @logical_name=name  from @shrink_log where id = @minrow and log_reuse_wait_desc = 'log_backup'
     --select db, name  from @shrink_log where id = @minrow and log_reuse_wait_desc = 'log_backup'
      --print @db    
   
    set @sql1='backup log [' + @db + '] to disk=''' +@bak_location+''+@db+replace (replace(convert(nvarchar(20),getdate(),120),':','_'),' ','_') + '.trn'''
    --print (@sql1)
    exec (@sql1)
    exec (@sql1)

    set @sql2='exec (''use[' + @db + '];dbcc shrinkfile(''''' +@logical_name +''''')'')'
    --print(@sql2)
    exec (@sql2)

    set @minrow =@minrow +1 
    end

end
insert into tbl_log_shrink_history
select *,getdate() from tbl_log_shrink_temp_load

-- select * from tbl_log_shrink_history
end


-- EXEC DBA_DB.DBO.USP_SHRINK_LOG_FILE '\\WIN-PC\DB_Backups\'