Mass Updates

The following SQL statement updates every row in the table large_tab:

update large_tab set col1 = 0

Each individual row update resulting from this statement is part of one transaction. If large_tab is a large table, this statement results in extensive logging, which can fill the transaction log before it completes, and results in the 1105 error (which indicates that the transaction log is full). If the transaction log is filled before the statement completes, the portion of the transaction that was processed is rolled back. This process can require significant time and server resources.

In addition to causing excessive logging, unnecessarily large transactions have disadvantages in terms of both the number and the type of locks held as a result. For example, an exclusive table lock is usually acquired for a mass update to reduce locking overhead and complete it as soon as possible, and this type of lock prevents all other users from modifying the table during the update.

You can sometimes avoid problems associated with mass updates by breaking up large transactions into several smaller ones and then executing DUMP TRANSACTION statements between the different parts. For example, the single update statement in the previous example could be broken into two or more pieces as follows:

update large_tab set col1 = 0 
where col2 < x
go
dump transaction database_name with truncate_only 
go

update large_tab set col1 = 0
where col2 >= x
go

dump transaction database_name with truncate only
go

In this example, x is a value selected so that about half the rows in the table meet the condition col2 < x and the remaining rows meet the condition col2 >= x.

Transaction logs saved for media failure recovery should be dumped to a device, and the WITH TRUNCATE_ONLY option of the DUMP TRANSACTION statement should not be used. After you execute a DUMP TRANSACTION WITH TRUNCATE_ONLY statement, you must dump the database before you can dump the transaction log to a device.