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 operationof
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.
Free online sql Server dba learning
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute providing SQL Server and T-SQL (SQL Server) for Developer Training, SQL Data base SQL DBA Training and strong core of Business Intelligence (MSBI / SQL BI) core Training and we will guide you for database administrator training and placement, sql developer training and placement and we will prepare you for microsoft sql server database certifications
We have been working and providing SQL DBA classes partnered with numerous consultancies in USA. We carryout training on SQL Server 2008 to 2017 versions. We ensure that every one of our sessions are especially intelligent and very much organized with questions illuminations during and after each course.
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE