Contents of transaction log file in more detail

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 committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.

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.

As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.

I'll explain how commercial tools work, on the ApexSQL Log example

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?

Yes, they read the LDF file (online or detached) and trn files (transaction log backups), find what transaction has happened, and create a script that will do the same, or the opposite.

Note however, that the undo and redo script don't have to be exactly the same as the ones executed, but the effect will be exactly the same.

For example, if the executed script was:

DELETE FROM [Person].[AddressType] WHERE Name  = 'New Loc22'

The transaction log will record that the row in the table with the column values 9, 'New Loc22', '41BC2FF6-F0FC-475F-8EB9-CEC1805AA0F6', and '2002/06/01 00:00:00.000' is deleted. From the table structure, the tool will read that the Primary key is the AddressType column, and will create the following redo script:

DELETE FROM [Person].[AddressType] WHERE [AddressTypeID] = 9

Note that the transaction is tied to the Primary key column, not to the column used in the original where clause. Similarly, the undo script will be:

INSERT INTO [Person].[AddressType] ([AddressTypeID], [Name], [rowguid], [ModifiedDate]) VALUES (9, N'New loc22' COLLATE SQL_Latin1_General_CP1_CI_AS, '41bc2ff6-f0fc-475f-8eb9-cec1805aa0f6', '20020601 00:00:00.000')

Disclaimer: I work for ApexSQL as a Support Engineer