Log Manager

Like other major RDBMS products, SQL Server ensures that all write activity (inserts, updates, and deletes) performed on the database is not lost if something interrupts the SQL Server online status (for example, power failure, disk drive failure, fire in the data center, and so on). The SQL Server logging process helps guarantee recoverability. Before any implicit (single Transact-SQL query) or explicit (transaction that issues BEGIN TRANSACTION, COMMIT, or ROLLBACK statements) transactions can be completed, the SQL Server log manager must receive a signal from the disk subsystem that all data changes associated with the transaction have been written successfully to the associated log file. This rule guarantees the transaction log can be read and reapplied in SQL Server when the server is turned on after an abrupt shut down during which the transactions written into the data cache are not yet flushed to the data files. Flushing data buffers are checkpoint or lazy writer responsibility. Reading the transaction log and applying the transactions to SQL Server after a server stoppage is referred to as recovery.

Because SQL Server must wait for the disk subsystem to complete I/O to SQL Server log files as each transaction is completed, disks containing SQL Server log files must have sufficient disk I/O handling capacity for the anticipated transaction load.

The method for monitoring disk queuing is not the same for SQL Server log files  and for SQL Server database files. You can use the Performance Monitor counters SQL Server: Databases database instance : Log Flush Waits Times and SQL Server: Databases database instance : Log Flush Waits/sec to view log writer requests waiting on the disk subsystem for completion.

For highest performance, you can use a caching controller for SQL Server log files if the controller guarantees that data entrusted to it is written to disk eventually, even if the power fails. For more information about caching controllers, see "Effect of On-board Cache of Hardware RAID Controllers" later in this chapter and SQL Server Books Online.