What Information Is Logged

To ensure that a transaction is recoverable, when a transaction is committed, SQL Server logs all information about the transaction in the transaction log. The amount of data logged for a single transaction depends on the number of indexes affected, the amount of data changed, and the number of pages that must be allocated or deallocated.

Depending on the transaction, certain other page management information might also be logged. For example, when a single row is updated, the following records might be written in the transaction log:

• A data delete record, including all data in the original row
• A data insert record, including all data in the modified row
• One index delete record for each index affected by the change
• One index insert record for each index affected by the change
• One page allocation record for each new data or index page required
• One page deallocation record for each data or index page freed

To ensure the recoverability of all transactions performed on SQL Server, transaction logging cannot be turned off. Any single Transact-SQL statement (or set of Transact-SQL statements within a BEGIN...COMMIT/ROLLBACK TRANSACTION block) that modifies data is considered a transaction and is therefore logged. You can, however, limit the amount of logging performed for specific operations, such as:

• Bulk copying data into a database using bcp in fast mode
• Performing a SELECT INTO query

Important These non-logged operations cause the transaction log to get out of sync with the data in a database, which makes the transaction log useless for database recovery.

After a non-logged operation has been performed, the transaction log can be truncated, but it cannot be dumped to a device. To allow the log to be dumped to a device, you must execute a DUMP DATABASE statement, which creates a new point of synchronization between the database and the transaction log.