Search This Blog

Friday, August 10, 2007

Transaction Log Size grows huge of Case Scenario mentioned below

A particular big transaction with UPDATE statement affects 1 million records.
Every time I run this SP/Query, my transaction log is growing too much and some times it is failing. What all the remedy actions you suggest?


Answer would be split the big transaction into multiple small transactions

Lets suppose it is just a simple update

Begin tran bigger_tran
Update tableA
Set col = value
Where condition
Commit tran


Now the split transaction would be

declare @ctr int
declare @trancount int
declare @ln_error int
declare @ln_rowcount int

set @ctr =1
set @trancount = 0

/* This updates only 500 records at a time */

Set ROWCOUNT 500

while (@ctr <> 0)
begin

begin tran chunk_tran

update big_tran_to_small_tran
set j = null
where j is not null

select @ln_error = @@error, @ln_rowcount = @@rowcount

if @ln_error <> 0
begin
rollback tran chunk_tran
break
end
if @ln_rowcount = 0
begin
set @ctr=0
end
else
begin
commit tran chunk_tran
set @trancount = @trancount +1 -- verification only
end

end

set rowcount 0

No comments: