Does SQL Server log record uncommited operations?

Question

I often see statements like sql server log records every transction and opeation.

But I am confused about what happens when a transaction is eventually rolled back.

Say an explicit transaction has 3 statements: statement A, statement B, statement C, and finally a rollback statement D.

Now say when the execution has not reached the rollback statement D, will the modifications resulting from statements A through C get recorded to sql server log?

Understanding 1:

Statements A through D all get recorded. SQL Server records everything, no matter what.

Understanding 2: Modifications are only stored somewhere in memory, and only recorded to log when SQL Server see a commit statement. If it turns out to be a rollback statement, SQL Server simply ignore the transacrion, no writing to log happens because it serves no purpose. In other words, SQL Server logs when there is a net result before and after the transactions.

Both seems logical, at least to me but they can't both be right. Thanks for any help.

Answer 1

Understanding 1 is correct. SQL Server records every operation that changes data to the transaction log. A rollback is a change to data so it also records that to the transaction log as well. As statement A run it will write data to the transaction log and will also reserve data in the transaction log in case statement A needs to be rolled back. The same is true for B and C. When you rollback the transaction more information will be written to the log.

There are plenty of ways to see this in action so below is a quick demo. Here is the query that I'll use to see what was written to the log:

SELECT 
  COUNT(*) transaction_count
, SUM(database_transaction_log_bytes_used) used_bytes
, SUM(database_transaction_log_bytes_reserved) reserved_bytes
FROM sys.dm_tran_database_transactions
where database_id = 10;

My table:

create table TLOGDEMO (FLUFF VARCHAR(1000));

BEGIN TRANSACTION

Query A uses minimal logging:

INSERT INTO TLOGDEMO WITH (TABLOCK)
SELECT REPLICATE('A', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After A:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║   24006640 ║      175429451 ║
╚═══════════════════╩════════════╩════════════════╝

Query B does not use minimal logging:

INSERT INTO TLOGDEMO
SELECT REPLICATE('B', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After B:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7352935708 ║     1613986255 ║
╚═══════════════════╩════════════╩════════════════╝

Query C changes less data:

INSERT INTO TLOGDEMO
SELECT REPLICATE('C', 1000)
FROM master..spt_values c;

After C:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7355821748 ║     1614545331 ║
╚═══════════════════╩════════════╩════════════════╝

Now I will issue a ROLLBACK and query the DMV while the rollback happens. Below is a table of a few snapshots:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║ 1                 ║ 7393305528 ║ 1573797677     ║
║ 1                 ║ 7458767420 ║ 1502635737     ║
║ 1                 ║ 7682482356 ║ 1259440979     ║
║ 1                 ║ 7803881368 ║ 1127471233     ║
║ ...               ║ ...        ║ ...            ║
╚═══════════════════╩════════════╩════════════════╝

During the ROLLBACK, the used bytes increases and the reserved number of bytes decreases. That's because SQL Server is using the space it set aside before to undo the transaction. To undo the transaction it must change data so it writes more data to the log.

Answer 2

Modifications to database tables are written first to the log file, then to the tables themselves, first in memory and then, with an asynchronous process called CHECKPOINT, to disk. This mechanism is known as WAL (Write-Ahead Logging) and is common to all relational databases.

The log itself is first written to memory (to the log buffer, precisely) and then to disk, but nothing is touched on the database tables until the log has been written to disk.

This mechanism allows both rolling forward committed transactions and rolling back uncommitted transactions during the recovery process. Regarding your example, if something bad happened after statement C and you had a commit instead of a rollback (you can't know this in advance), without saving each and every step in the transaction, the RDBMS would have no way to recover the database in a consistent way and the transaction would not meet the D (durability) in ACID.

When some operations are rolled back, it's the data file that receives the net changes (via CHECKPOINT), not the log file.

Answer 3

Understanding 1 is correct, and spaghettidba and Joe have good explanations.

If you're interested in testing for yourself (on a test instance please), you can use the below script:

--create a database and table for testing
USE master
GO
CREATE DATABASE tranlogtest
GO
USE tranlogtest
GO
CREATE TABLE t1
(junk char(1))

CHECKPOINT
GO

BEGIN TRAN
INSERT t1 VALUES ('a')
INSERT t1 VALUES ('b')
INSERT t1 VALUES ('c')

ROLLBACK
INSERT t1 VALUES ('d')

SELECT *
FROM fn_dblog(NULL,NULL)

You'll see that SQL Server records everything, even the steps taken to undo operations.