Transaction Log Roles in SQL Server

What is logged in the transaction log?


SQL Server logs every event in a database to a more or lesser extent.
•When a transaction begins or ends
•Every update , insert or delete
•Drop and creation of tables and indexes.
•Extent and page allocations and de-allocations
•Truncation of tables
•All locks
Note: Some operations may be minimally logged when the database is in simple or bulk logged recovery model, such as bcp, BULK INSERT, SELECT INTO and SELECT … INSERT command.

 

Purpose of T-Log <or> What happen If transactions are not logged into transaction log:


A transaction log basically records all database modifications. When a user issues an INSERT, for example, it is logged in the transaction log.

This enables the database to roll back or restore the transactions, in the event of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server.

On restarting the server, database goes through the recovery process. During this recovery process , the transaction log is used to make sure that all committed transactions are written to respective data pages (rolled forward) and revert the uncommitted transaction that were written to data pages.

  

For example, let’s say Suman is using an application and inserts 2000 rows of data. While SQL Server is processing this data let’s say someone pulls the plug on the server or it shutdown . Because the INSERT statement was writing to the transaction log and it knows a failure occurred it will roll back the statement. If this wasn’t put in place, could you imagine having to sift through the data to see how many rows it inserted and then change the code to insert the remaining rows? Or even better, what if the application inserted random columns in no order and you had to determine what data was inserted and what data was left out? This could take forever!

 

Where LDF Files Play an Important Role\ Operations supported by the transaction log:


1.Individual transaction recovery:

If an application issues a ROLLBACK statement, or if the Database Engine detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.

2.Recovery of all incomplete transactions when SQL Server is started:

If a server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When an instance of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to make sure the integrity of the database is preserved.

3.Rolling a restored database, file, filegroup, or page forward to the point of failure:

After a hardware loss or disk failure affecting the database files, you can restore the database to the point of failure. You first restore the last full database backup and the last differential database backup, and then restore the subsequent sequence of the transaction log backups to the point of failure.

As you restore each log backup, the Database Engine reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, the Database Engine then uses the log information to roll back all transactions that were not complete at that point.

4.Supporting high availability and disaster recovery solutions:

The standby-server solutions, Replication ,Always On availability groups, database mirroring, and log shipping, rely heavily on the transaction log.

• The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database.
• In an Always On availability groups scenario, every update to a database, the primary replica, is immediately reproduced in separate, full copies of the database, the secondary replicas. The primary replica sends each log record immediately to the secondary replicas which applies the incoming log records to availability group databases, continually rolling it forward.
• In a log shipping scenario, the primary server sends the active transaction log of the primary database to one or more destinations. Each secondary server restores the log to its local secondary database.
• In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance which applies the incoming log records to the mirror database, continually rolling it forward.