Goal: understand SQL Server Transaction Log architecture.
This topic is complex, and I'm no expert; that's why I've attached several links from book online and other experts.
Before going to details about backup and restore. We must understand the basic of the SQL Server Transaction Log architecture. For more details, please see Paul Randal's post here.
Key Points:
* Every database has at least one transaction log (yes, including tempdb)
* Every record in a transaction log is identified by a LSN (Log Sequence Number). LSN is unique 10 bytes in size (4 bytes for VLF number + 4 bytes for block number + 2 bytes for log records number) , it's like a time stamp. Ex: 0000002e:0000027a:0001
* Realistically, it's not possible to use all LSN. Kimberly Tripp and Paul Randal provide an excellent example here. Based on the example, it would take 240 million years to generate 4 billion petabytes transaction log.
* Log records are the changes made to a database.
To view the log records, try these (they're not document on BOL)
DBCC LOG('AdventureWorks',3);
USE AdventureWorks;
go
SELECT * FROM sys.fn_dblog(null,null); --see Paul Randal blog here
* Every database has at least one transaction log (yes, including tempdb)
* Every record in a transaction log is identified by a LSN (Log Sequence Number). LSN is unique 10 bytes in size (4 bytes for VLF number + 4 bytes for block number + 2 bytes for log records number) , it's like a time stamp. Ex: 0000002e:0000027a:0001
* Realistically, it's not possible to use all LSN. Kimberly Tripp and Paul Randal provide an excellent example here. Based on the example, it would take 240 million years to generate 4 billion petabytes transaction log.
* Log records are the changes made to a database.
To view the log records, try these (they're not document on BOL)
DBCC LOG('AdventureWorks',3);
USE AdventureWorks;
go
SELECT * FROM sys.fn_dblog(null,null); --see Paul Randal blog here
* Log records are kept in sequence order in the transaction log; thus there's no performance gain if creating multiple transaction log files per database.
* Each physical log file is divided into multiple uneven virtual log files (VLFs). Think this as a hard drive has multiple partitions.
* VLF states (Brad McGehee sums it up nicely here)
> Unused: no log record is written to the VLF, and VLF is zeroed out
> Active: when 1 or more active log record exists in the VLF.
> Recoverable: No active log record exists but it's not available for SQL Server to use.VLF is not truncated.
> Reusable: No active log record exists in the VLF and the VLF is truncated.
* Each physical log file is divided into multiple uneven virtual log files (VLFs). Think this as a hard drive has multiple partitions.
* VLF states (Brad McGehee sums it up nicely here)
> Unused: no log record is written to the VLF, and VLF is zeroed out
> Active: when 1 or more active log record exists in the VLF.
> Recoverable: No active log record exists but it's not available for SQL Server to use.VLF is not truncated.
> Reusable: No active log record exists in the VLF and the VLF is truncated.
* Transaction log can include these: only one logical log, one or more truncated VLF(s), and one or more unused VLF(s).
* Transaction logs are circular. Think this as a race track. This means when logical log reaches end of the physical log file, the new log records are written to the beginning of the physical file. (read more here)
* Transaction logs are truncated after a log backup. Log truncation is not the same as log shrink. After a log truncation the size of the physical log file doesn't get any smaller.
* To reduce the physical log file size, shrink the log by use DBCC SHRINKFILE(). Ex: DBCC SHRINKFILE (AdventureWorks_Tlog, 10); -- Shrink a db's transaction log file to 10 Mb in size.
* Transaction Log truncation:
Under full and bulk-logged recovery model, VLF is truncated if a checkpoint occurred since the previous backup and after a transaction log backup. Under simple recovery model, log truncation occurs after a checkpoint. (read more here)
* missing any?
* Transaction logs are circular. Think this as a race track. This means when logical log reaches end of the physical log file, the new log records are written to the beginning of the physical file. (read more here)
* Transaction logs are truncated after a log backup. Log truncation is not the same as log shrink. After a log truncation the size of the physical log file doesn't get any smaller.
* To reduce the physical log file size, shrink the log by use DBCC SHRINKFILE(). Ex: DBCC SHRINKFILE (AdventureWorks_Tlog, 10); -- Shrink a db's transaction log file to 10 Mb in size.
* Transaction Log truncation:
Under full and bulk-logged recovery model, VLF is truncated if a checkpoint occurred since the previous backup and after a transaction log backup. Under simple recovery model, log truncation occurs after a checkpoint. (read more here)
* missing any?
Insights:
1. SQL Server transaction log and database recovery model are linked together.
2. Good practice to perform regular log backup or the transaction log file will grow very large.
3. Log backup does not shrink the transaction log file. It's only deallocate the VLFs for later user.
4. last but not least, understand SQL Server transaction log is one of the key to be an exception DBA. This topic is complex and fun.
1. SQL Server transaction log and database recovery model are linked together.
2. Good practice to perform regular log backup or the transaction log file will grow very large.
3. Log backup does not shrink the transaction log file. It's only deallocate the VLFs for later user.
4. last but not least, understand SQL Server transaction log is one of the key to be an exception DBA. This topic is complex and fun.
Credits:
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
http://msdn.microsoft.com/en-us/library/jj835093.aspx
http://msdn.microsoft.com/en-us/library/ms190411.aspx
https://docs.google.com/viewer?a=v&q=cache:kj_IFj-I2NgJ:www.bradmcgehee.com/wp-content/uploads/presentations/St%2520Louis_Inside%2520the%2520SQL%2520Server%2520Transaction%2520Log.pdf+&hl=en&gl=us&pid=bl&srcid=ADGEESh64yODlanM9ljyhctpdPBKxGQbITq_3syZSOAQ5HmL9wp53AB_z5sP1C49eRbRDOekzJUQ7JoaOUKEMnnX5Tf0nbdTKdAXt5m-5h00iyz-0-JLebfw3zVbkbxNR7Jqz2BMS_Wz&sig=AHIEtbTH3gOfncEzWzTJlo0kuAAvivqOdg
http://msdn.microsoft.com/en-us/library/ms189573.aspx
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-it-possible-to-run-out-of-log-sequence-numbers-137120
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
http://msdn.microsoft.com/en-us/library/jj835093.aspx
http://msdn.microsoft.com/en-us/library/ms190411.aspx
https://docs.google.com/viewer?a=v&q=cache:kj_IFj-I2NgJ:www.bradmcgehee.com/wp-content/uploads/presentations/St%2520Louis_Inside%2520the%2520SQL%2520Server%2520Transaction%2520Log.pdf+&hl=en&gl=us&pid=bl&srcid=ADGEESh64yODlanM9ljyhctpdPBKxGQbITq_3syZSOAQ5HmL9wp53AB_z5sP1C49eRbRDOekzJUQ7JoaOUKEMnnX5Tf0nbdTKdAXt5m-5h00iyz-0-JLebfw3zVbkbxNR7Jqz2BMS_Wz&sig=AHIEtbTH3gOfncEzWzTJlo0kuAAvivqOdg
http://msdn.microsoft.com/en-us/library/ms189573.aspx
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-it-possible-to-run-out-of-log-sequence-numbers-137120
No comments:
Post a Comment