Most SQL Server processing is logged in the syslogs transaction log table. Each database (including the master, model, and tempdb system databases) has its own syslogs transaction log table. As the database is modified, the transaction log grows until it is truncated, either by a DUMP TRANSACTION statement or automatically (if the trunc. log on chkpt. option is set).
Caution Do not try to access the syslogs transaction log table. It is for internal use by SQL Server only.
The transaction log records data modification requests (UPDATE, INSERT, or DELETE statements) before they are executed. When a transaction begins, a begin transaction event is recorded in the log. This event is used during automatic recovery to determine the starting point of the transaction.
As each data modification statement is received, it is recorded in the log. The change is always recorded in the log before that change is made in the database itself. This type of log is called a write-ahead log.
At the end of the transaction, a commit transaction record is logged. This delineates the transaction and also allows automatic recovery to query this log record and find out if the transaction completed successfully.
Caution It is imperative that SQL Server know that a write has completed. A write-caching disk controller compromises the ability of SQL Server to manage transactions by making it appear as if the write-ahead logging has completed, even though it has not. This can result in errors such as error 605 and can also cause database corruption. For this reason, do not use a write-caching controller with SQL Server unless it can guarantee that a write will complete.
The transaction log is shared by all users of the database. Multiple changes are frequently recorded each time a log page is written to the database device. This greatly improves I/O efficiency.