SQL Server - contents of transaction log file in more detail

Question

I have a question regarding transaction log (let’s just call it LDF for short) contents. I am assuming a database with full recovery model.

I have read that LDF file contains (logs) each and every operation to the database (that is in full recovery mode). How is it different from logging during BEGIN TRAN; COMMAND(s); COMMIT? I am asking because apparently you can roll back transactions, but you cannot roll back standard commands (in full recovery mode). So I guess that during transaction the contents being logged into the LDF file are different than in regular full recovery logging. Is that right? How is it different? Is it only the inclusion of “undo” operations for each action?

And on related note I have heard that there are commercial tools to “rollback/undo” standard queries using full recovery LDF file. How do they do it? Do they analyze the LDF contents and try to come-up with inverse/undo operations? Any help would be appreciated.

Originally my question was a little broader, but someone closed it as apparently it was too broad. However it might help to explain what I want to ask so I post a link here if somebody would be interested. I will ask the remaining points in separate question.

SQL Server Full Recovery Mode - rollback last action without transaction?

Thanks guys.

Answer 1

Expanding on my answer above, and making it a real answer...

The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.

This is also why you can't rollback this INSERT: it's already commited. So the rule is the same as explicit transactions: you can't rollback once they've been commited.

You can see what I mean directly from inside SQL Server.

Microsoft ships SQL Server with a DMF called sys.fn_dblog that can be used to look inside the transaction log of a given database.

For this simple experiment I'm going to use the AdventureWorks database:

USE AdventureWorks2008;
GO

SELECT TOP 10 *
FROM dbo.Person;
GO

INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;

BEGIN TRAN;
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
GO

SELECT *
FROM sys.fn_dblog(NULL, NULL);
GO

Here I'm doing two inserts: one with and one without an explicit transaction.

In the log file you can see that there's absolutely no difference between the two:

The red one is the INSERT within an autocommit transaction and the blue one is the INSERT with an explicit transaction.