Batch Update in SQL Server

I recently faced an issue with batch updates in SQL server. There was a table in SQL server 2000 which had 1 billion records and I had to introduce a new column to that with a default value. Adding a default value along with the alter script failed with an exception as shown below.

Error: 9002, Severity: 17, State: 2
The log file for database '%.*ls' is full.

Further info: http://support.microsoft.com/kb/317375

Hence I used a script which updates the records in a batch as following

Set nocount on
DECLARE @batch_size int, @rowc int
SELECT @batch_size = 20000
SELECT @rowc = @batch_size
SET ROWCOUNT @batch_size
WHILE @rowc = @batch_size
BEGIN
Begin Tran
Update dbo.<> Set <> = <> where <> is null
SELECT @rowc = @@rowcount
Commit Tran
END
SET ROWCOUNT 0
Set nocount off


This had few drawbacks it was very slow (took 10 hours) to update ½ a billion records and caused an exception as following

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken


I had to rewrite the script as following


SET NOCOUNT ON
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tMonths%')
BEGIN
DROP TABLE #tMonths
END
SELECT [Month]
INTO #tMonths
FROM dbo. tblHoldings_Securities_Total
GROUP BY [Month]
DECLARE @dtMonth DATETIME
DECLARE @sMonth VARCHAR(30)
DECLARE @rc INT
DECLARE cur_months CURSOR FAST_FORWARD
FOR SELECT [Month] FROM #tMonths ORDER BY [Month]
OPEN cur_months
FETCH NEXT FROM cur_months INTO @dtMonth
WHILE @@fetch_status = 0
BEGIN
set @sMonth = cast(@dtMonth as varchar)

UPDATE dbo.tblHoldings_Securities_Total
SET Sec_identifier = 1
WHERE [Month] = @dtMonth
AND Sec_identifier IS NULL
SELECT @rc = @@ROWCOUNT
RAISERROR ('Month: %s; Updated count: %d',10,1, @sMonth, @rc) WITH NOWAIT
FETCH NEXT FROM cur_months INTO @dtMonth
END
CLOSE cur_months
DEALLOCATE cur_months


Advantages

1.The above method used grouping logic based on an Indexed column.
2.Completed in 4 hours.