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
http://msdn.microsoft.com/en-us/library/ms345414.aspx
>> 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
Nice post.