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.

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

Thanks guys.

share|improve this question
    
The difference is that what you call "standard commands" have implicit transactions, 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 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 are commited. –  ivanmp Jan 3 '13 at 13:07

1 Answer 1

up vote 7 down vote accepted

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:

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

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.