INF: SQL Server Update Transaction Log Information

ID: Q67174

The information in this article applies to:

SUMMARY

The following information discusses how the transaction log file is updated.


MORE INFORMATION

Each transaction log record is composed of a 16-byte header, plus a variable length row for the type of transaction.

When an update is performed and a nonclustered index is used on the table, SQL Server will delete the old version of the row, and then insert the new version of the row. This is true for both data rows and index rows. Please note that SQL Server will perform an "update in place" under certain restrictions. For an outline of these restrictions, query on the following words:

SQL Server and criteria and insert and update

The update is treated as a complete transaction. In the case of a table with a nonclustered index, records are logged to indicate an intent to update. One record is logged with the data to insert, and one record is logged with the location of the data to delete. Next, a record is logged for each data row and index row that is deleted. As each insert is processed, the location of the updated row is determined. A record is then logged with this location and a pointer to the log record that contains the data. Finally, for each index entry inserted, a record is logged.

For example, an update of two records would be logged as follows: 

   Begin transaction
   CMD
   Intent to delete record 0
   Intent to insert data "hello world" log record (2054, 12)
   Intent to delete record 1
   Intent to insert data "hello work"  log record (2054, 14)
   Delete index #1
   Delete index #2
   Delete record data "good bye world"
   Delete index #1
   Delete index #2
   Delete record data "good bye work"
   Insert index #1
   Insert Index #2
   Insert data from (2054,12) to page 40
   Insert index #1
   Insert Index #2
   Insert data from (2054,14) to page 41
   End Transaction


In summary:
  1. The intent to delete and insert the data is logged.


  2. All the deletes are logged.


  3. All the inserts are logged.


Given the above information, the minimum size of the log for this type of update is as follows: 

   For each update assuming that there are two indexes:

   2 Rows for Index Deletes
   2 Rows for Index Inserts
   1 Row for data Delete
   1 Row for deferred insert
   -
   6 x 16 bytes = 96 bytes

   1 Row for deferred delete with a fixed length of 24 bytes
   1 Row for location of insert with a fixed length of 52 bytes
   ------------------------------------------------------------

   Total 172 bytes x #rows to be updated

   + 2 x the size of data
   + 2 x the size of index

In addition to the inserts and deletes, there are also log records for both the begin and end transactions, and a log record for the command. This also does not take into consideration potential page splits, allocation and deallocation of pages, extent allocation, and deallocation, which also generate log records in the transaction log.

Additional query words: Database repair 


Keywords          : kbother SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 10, 1999