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