DBA

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

Advertisements

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://www.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\'

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: