Online sql dba learning

​fREE

microsoft sql training online free

 Meet the Transaction Log
In this chapter, we start with an overview of how SQL Server uses the transaction log, focusing on the Write Ahead Logging mechanism that ensures transactional consistency and durability during normal operation
of

a SQL Server database. We then offer an initial overview of two of the most significant ways in which it impacts the life of a DBA, namely database restore and recovery, and disk space management.


How SQL Server Uses the Transaction Log
In SQL Server, the transaction log is a physical file, identified conventionally, though not compulsorily, by the extension LDF. SQL Server creates one automatically for any new database, along with the primary data file (commonly identified by the MDF extension), which stores the database objects and the data itself. Generally, each database will have a single transaction log file. It is possible to implement the log as multiple physical files, but SQL Server always writes to the log sequentially; it cannot, and does not, write in parallel to multiple log files and so there is no advantage to having multiple files from the perspective of log throughput. 


Whenever T-SQL code makes a change to a database object
(DDL), or the data it contains, not only is the data or object updated in the data file, but details of the change are also recorded as a log record in the transaction log. Each log record contains the details of a single, specific change made to the database (such as the insert of a single row), so it may require a whole series of log records to describe fully the effects of a single transaction. Certain of these log records will record the ID of the transaction that performed the change, when that transaction started and ended, which pages changed, the data changes that were made, and so on. SQL Server knows how to link these log records together in the right order (more on this in Chapter 2) and so can construct a complete description of the actions of each transaction, which can be performed again as a part of redo, or rolled back as a part of undo, during a crash recovery operation.


Transaction Log