Update 100 Million Records

I Have a one big transaction with UPDATE stmt, which 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?

Ensure that Always your transaction size should be small enough.

In this case, we need to split the big transaction into multiple small transactions.

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


Spliting the transaction

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

set @ctr =1
set @trancount = 0
Set ROWCOUNT sa

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