SQL Server full log issue -- how to update the database?

Question

I am working with SQL Server 2000, and trying to change the data type of a field from varchar to nvarchar, so that it can handle international characters. However, there is already a lot of data in that table, and when I try to save the change, I get the following error:

Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'AppTest_Apps' is full. Back up the transaction log for the database to free up some log space.

This is a one-time update -- how do I get around the error?

Answer 1

At first look, I see two ways:

1. Just backup the transaction log. Something like BACKUP LOG dbanme TO DISK='c:\dblog.bak'
2. bring your database into SIMPLE recovery mode (e.g., in Enterprise Manager: database-> options -> recovery model, select Simple)

Answer 2

You may need to allow the log file to grow larger (see the options on the log file), or backup and shrink it. http://support.microsoft.com/kb/272318

Answer 3

Try running a transaction loop that commits every n number of records. So could rename the current table from X to Y. You can do this with this command sp_RENAME '[OldTableName]' , '[NewTableName]'.

Recreate the X with the new datatype column set and then batch insert from Y back into X committing every loop. By inserting with transaction batch you can keep your log growth under control by committing every n number of records inserted.

Pseudo code

Get @max_id from Y int @max_id 
Get min_id from Y into @current_value
Loop until @current_value <= @max_id

BEGIN TRAN
INSERT INTO Y
FROM X
WHERE seq_id >= @current_value and less than @batchsize + @current_value
COMMIT TRAN