How does the transaction log record a transaction?

Question

We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS!

Ah, but there could be several ways for SQL Server to record the transactions. So the question is, how does it record the transactions?

Here are the following ways I envisage it is working:

1. It merely makes a record of which statement was run. In this case, the transaction log contains the actual text of the DML statement, something like this:

   "INSERT INTO test_database.dbo.Test_People (FirstName,LastName) VALUES ('James' ,'Smith')"

In this case the transaction log contains the whole of what is quoted above.

Then, in a transaction log restore (after a full backup restore), SQL Server will actually execute the statement contained in the transaction log, against the database, to update it.

2. It stores a record of the entire row of data, with all its values (even those values that were not updated), and the date at which the row was modified/inserted.

Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting it or overwriting the same row in the table with the data in the transaction log.

3. It stores a record of the entire data page, with all its values (even those values that were not updated), and the date at which the data page was modified/updated/deleted.

Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting the page or overwriting the page with the same pageID.

Or, perhaps it is none of the above. Does anybody know?

Answer 1

I could not put this as comment so here it is for you. It is not compete answer to your question because your question can either be correctly answered by person working in Microsoft or Paul Randal(I guess). All I can say for all paractical purposes every information is logged.

You can read the contents of log file using undocumented command

select * from fn_dblog(Null,Null)

If you run it you can see lot of information related to database, pages,extents,locks ect. But it would be difficult for you to extract information from it as it requires a level of expertise to decipher the output.

If you read Books Online document SQL Server Transaction Log architecture and Management it says

Many types of operations are recorded in the transaction log. These operations include:

•The start and end of each transaction.

•Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

•Every extent and page allocation or deallocation.

•Creating or dropping a table or index.

Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

AFAIK there is no information about query which was fired but the changes which query made is written in transaction log. Changes made to page, extents , locks that were taken, resources that were locked.