Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

http://dba.stackexchange.com/questions/31301/sql-server-full-recovery-mode-rollback-last-action-without-transaction

share|improve this question

2 Answers 2

up vote 10 down vote accepted

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:

Autocommit vs Explicit Transactions

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

share|improve this answer
3  
Even though the transactions aren't explicit it might be better to say auto commit transaction as implicit transaction as a term means something different –  Martin Smith Jan 3 '13 at 13:45
2  
@NeverStopLearning you can't rollback an autocommit transaction exactly as you can't rollback an explicit commited transaction. No difference. You say that you don't see a commit in the log, but I think you missed it. Both transactions are exactly the same: LOP_BEGIN_XACT, LOP_INSERT_ROWS, LOP_COMMIT_XACT. You say that you can rollback an explicit transactions, and that's true, but only because you are still inside the transaction until you commit it! –  ivanmp Jan 3 '13 at 15:21
1  
@NeverStopLearning you could create a Trigger on a table and inside this trigger you could issue a ROLLBACK statement, and it would work exactly the same for both transactions, because "under the hood" SQL Server creates a transaction for you. So even though you didn't supply a "BEGIN TRAN" statement, it's still inside a full-fledged transaction and can still be rolled back in case something goes wrong while processing it. –  ivanmp Jan 3 '13 at 15:24
1  
@NeverStopLearning there are some things related to this (such as log truncation during backup, etc), but for the sake of answering your question, it's an "artificial limitation" (if you think about it as a limitation instead of a feature). Please note that if you SET IMPLICIT_TRANSACTIONS you will be able to rollback implicit transactions (and you will be required to explicitly COMMIT them). –  ivanmp Jan 3 '13 at 15:51
1  
@NeverStopLearning really? I'm not aware of that. TRUNCATE is a minimally logged operation as in it does not create a log entry for every row deleted (as DELETE would), just the operation itself, but as far as I know you can still do a ROLLBACK safely if you haven't committed yet. Where did you read this? :) –  ivanmp Jan 3 '13 at 16:07

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')

enter image description here

Disclaimer: I work for ApexSQL as a Support Engineer

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.