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:
Post a Comment