Seems like a simple enough question, right? This question (but more targeted, read on) was raised in an MCT forum. While the discussion was on-going and and I tried to come up with answers, I realized that this question are really several questions. First, what is a rollback? I can see three different types of rollbacks (there might be more, of course):
- Regular rollback, as in ROLLBACK TRAN (or lost/terminated connection)
- Rollback done by restore recovery. I.e., end-time of backup included some transaciton which wasn’t committed and you restore using RECOVERY, so SQL Server need to rollback this transaction (UNDO).
- Rollback done by crash (restart) recovery. I.e. SQL Server was shut down with some open transaction.
I’m going to try to show whether log records are removed or still present for these three types of rollback situations. I will use the fn_dblog function. This isn’t documented or supported, but search the internet and you will find how to use it. The result isn’t documented either, of course, so we have to guess a bit what the various values mean…
The TSQL script has some common parts (the same part executed repeatedly, once for each test):
- Create a database
- Make sure it is in full recovery
- Do a database backup
- Create a table (myTable)
- Insert a row into myTable. This last operation generates 5 log records for the myTable table: one for the PFS page, two for IAM pages, one format page for the heap and the last one is a LOP_INSERT_ROWS.
- Start a transaction
- Insert one more row into myTable. We now have one more log record for myTable (LOP_INSERT_ROWS). Looking at the transaction id for this last insert, we see two log records (one LOP_BEGIN_XACT and the LOP_INSERT_ROWS). Note that this transaction is now open!
Here is the above mentioned first part of the script:
USE master
IF DB_ID(‘x’) IS NOT NULL DROP DATABASE x
GO
CREATE DATABASE x
GO
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE x TO DISK = ‘C:\x.bak’ WITH INIT
USE x
CREATE TABLE myTable(c1 INT IDENTITY)
INSERT INTO myTable DEFAULT VALUES
SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE ‘%myTable%’
–5 rows
BEGIN TRAN
INSERT INTO myTable DEFAULT VALUES
SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE ‘%myTable%’
–6 rows
SELECT * FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE ‘%myTable%’
ORDER BY [Current LSN] DESC)
–2 rows Now, on to the different cases:
1. Regular rollback, as in ROLLBACK TRAN.
It seems reasonable to me that SQL Server will just add some “rollback transaction” log record here. So, let’s try that (continuing on above first part)… We now have 7 log records for myTable, with an added LOP_DELETE_ROWS which undo the previously insert. And for our transaction ID, we have 4 rows, with added two rows being LOP_DELETE_ROWS (compensation log record) and a LOP_ABORT_XACT.
–Regular ROLLBACK
ROLLBACK TRAN
SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE ‘%myTable%’
–7 rows
SELECT * FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE ‘%myTable%’
ORDER BY [Current LSN] DESC)
–4 rows 2. Restore recovery
Now, what does this really mean? Restoring a transaction log backup is a good example. SQL Server read log records from the transaction log backup file and writes them into the LDF file. This is what we call the “data copy” phase. Then SQL Server performs REDO (a.k.a. roll forward). And finally, SQL Server performs UNDO (roll back), assuming we don’t do the restore using the NORECOVERY option. Restoring from a database backup isn’t any differene except the log records are of course read from the database backup file.
Here it seems likely that SQL Server will wipe more or less anything from the LDF file as soon as the database is restored and brought on-line. Why? The log in this newly restored database can’t serve as a starting point for a restore operation for this database. You first need a database backup. So, no use hanging onto log records either! Let’s see if we can verify that:
–Restore recovery
–Do this from different connection
BACKUP LOG x TO DISK = ‘C:\x.bak’
–We now have open transaction in database!
–Perform RESTORE
ROLLBACK
USE master
RESTORE DATABASE x FROM DISK = ‘C:\x.bak’ WITH REPLACE, NORECOVERY
RESTORE LOG x FROM DISK = ‘C:\x.bak’ WITH FILE = 2, RECOVERY
–Rollback was done, and database berought online.
–What log records do we have?
SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE ‘%myTable%’
–0 rows
–Use the transaction ID from earlier SELECT
SELECT * FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = ‘0000:00000203’
–0 rows So, SQL Server will remove user-defined stuff from LDF file after restore recovery was performed. Makes sense.
3. Crash recovery (a.k.a. restart or startup recovery)
I couldn’t really guess here. So, lets give it a spin immediately and see:
–Crash recovery
–Do below from separate connection
SHUTDOWN WITH NOWAIT
— startup SQL Server and examine the log records:
SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE ‘%myTable%’
–5 rows
–Use the transaction ID from earlier SELECT
SELECT * FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = ‘0000:00000204’
–0 rows Hmm, so SQL Server removes the log record after the rollback was performed. Makes sense.
But this got me thinking some more. How can this be done… physically? SQL Server would just “reverse” the head of the log a bit. But what if we have later transactions for other connections, which has been committed? SQL Server can’t ignore those, of course. These need to be kept in the LDF file for subsequent log backups. OTOH, I doubt that SQL Server will somehow physically delete things “in the middle” of an ldf file. Time for yet another test:
–Crash recovery, part two
–Do below from separate connection
–First something which creates more recent log records
CREATE TABLE y(c1 INT) INSERT INTO y(c1) VALUES(1)
SHUTDOWN WITH NOWAIT
— startup SQL Server and examine the log records:
SELECT * FROM fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE ‘%myTable%’
–7 rows
–Use the transaction ID from earlier SELECT
SELECT * FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = (SELECT TOP(1) [Transaction ID]
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE ‘%myTable%’
ORDER BY [Current LSN] DESC)
–4 rows Now the log records for our rolled back transaction are still there! So, just as when we did a regular rollback, SQL Server inserted a LOP_DELETE_ROWS to reflect the undo of the INSERT, and then a LOP_ABORT_XACT.
Conclusion
Isn’t it beautiful when it all makes sense? Here are my conclusions, whether log records are kept or removed from transaction log file (LDF) for various types of rollback scenarios:
- Regular rollback. Log records are not removed. Compensation log records are logged, reflecting undo of the modifications, and then an LOP_ABORT_XACT is logged.
- Restore recovery. Log records are removed.
- Crash recovery. It depdends. If you have a transaction which is at the very head of the log, then those log records can be removed. If there are other, subsequent committed transactions, then compensation log records are logged, reflecting undo of the modifications, and then a LOP_ABORT_XACT is logged.