INF: SQL Server Update Transaction Log Information

 Last reviewed: April 25, 1997
Article ID: Q67174

The information in this article applies to: 

  - Microsoft SQL Server version 4.2 for OS/2

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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.