Transaction Log Internals

Transaction Log :

>> Transaction log is tracks transaction on that database.

>> There should be at least one  transaction log (ldf) file for every database.

>> Transaction log is always write in sequential manner , Only at redo state it will read in reverse order. (mostly Write operations occurs in TLog file)

>> previous to Sql server 2005, triggers reads Tlog , from Sql server 2005 –triggers are using Versioning – TempDB

>> If adding multiple ldf file, it will fill first ldf and then sequentially others, so no performance gain just space management.

>> It never uses Instance file initialization(for data file only) , initial growth is always Zeroed out.

>> Checkpoint will release/clear log file in simple recovery model, and after transaction log Backup for Full/Bulked Logged Recovery model.

>> Log file contains  group of LSN (Log Sequential Number) which can be filled once transaction is flushed to disk.

>> LSN can be reused  for Free LSN and work in round robin system, active LSN cannot be written.

>>   DBCC Loginfo: 2 : Active Transaction, 0 Free LSN

>> Truncate table command is also logged

>> as Log file is mostly for Write. Use appropriate RAID or disk for this.

>> Zeroed Virtual Log File Boot page(page9 file 1) keep record of last log active file.

>> Number of VLF’s are created depending upon the growth of the log file as follows:
Less than < 64mb – 4 VLFs

64mb to 1gb -8 VLFs

Above >1gb -16 VLFs

DBCC Loginfo  : Parity column 64 first then 128  and again 64 – round robin, for reused.

dbcc opentran – check any open transaction on database

dbcc sqlperf(logspace) –%log size full

>> Log file should not be of very small/large size – it will impact on log clearing task ( Checkpoint/Log Backup)  and degrade the performance also degrade performance for log growth.

*>> Every page header contains LSN value related to the dml transaction associated with it. at the same it LSN maintains the data page information( from memory/disk) changes for that transaction

This entry was posted in Internal, Transaction Log and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.