Readers bump into this blog because it tries to simplify some of the most complex topics into a digestible format. I am a strong believer of this and try to bring some of these topics from time-to-time. Even I find some of these topics confusing, if I can understand them in simple words – will they reach the blog immediately as part of notes of my learning. This blog is no different in this regards because I have been wanting to write about TLogs for a while and the opportunity just came from. Ever since I wrote about the WAL protocol, this followup blog has been pending.
The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it.
Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.
Log records for data modifications record either the logical operation performed or the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.
The steps to recover an operation depend on the type of the log record:
- Logical operation logged:
- To roll the logical operation forward, the operation is performed again.
- To roll the logical operation back, the reverse logical operation is performed.
- Before and after image logged:
- To roll the operation forward, the afterimage is applied.
- To roll the operation back, the before image is applied.
Many types of operations are recorded in the transaction log. These operations include:
- The start and end of each transaction.
- Every data modification (insert, update, or delete). This includes changes by the system stored procedures or data definition language (DDL) statements to any table, including system tables.
- Every extent and page allocation or deallocation.
- Creating or dropping a table or index.
Rollback operations are also logged. Each transaction reserves space in the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.
The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to make a full recovery of the database. No part of the active log can ever be truncated.
I hope this was a complete dope and dump of a lot of Transaction log basics in one blog and I will try to bring up others for later blogs. Please let me know if you learnt something new today.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
This sounds familiar to me, as I come from oracle administration, where the redologs are a main focus. In Oracle, a “drop tablespace” command would be the biggest problem in db recovery scenarios. The mssql equivalent to an oraxcle tbs would probably be a database, correct? What impact do “create database” and “drop database” have on mssql transaction logs? Do you find someting in the transaction log of the master db?
what ever the transactions done on database every transaction will be recorded into log file. whether it is a insert/updated/delete. Any transaction will recorded in log file. if you have 1000 inserts(bulk inserts) then each and every insert will be one transaction, so 1000 inserts will be stored as 1000 transactions. In SQL Server, all the data will be stored in pages and contexts. Create or drop database will be each individual(different) transactions.Master database is the system database it will be there in default location in c:\programfiles\…. etc… User database will be placed in some other place.