12

Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log backup actually contain?

  1. Transaction A commits
  2. Transaction B opens
  3. Log backup begins
  4. Transaction C opens
  5. Transaction B commits
  6. Log backup completes
  7. Transaction C commits
12

The log backup file shall contain all the transactions (including the ones that hasn't been committed yet) up to the moment the backup finished running and you can verify it as follows:

CREATE DATABASE MyDB;
ALTER DATABASE MyDB SET RECOVERY FULL;

USE MyDB;
CREATE TABLE LogTest (numbers int);

Once created the database and the table that's gonna be used on the test, take the first full backup so that it will be possible to take a log backup later.

BACKUP DATABASE MyDB 
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_FULL.bak';

Now that a full backup was taken let's insert some data on the table:

USE MyDB;

INSERT INTO LogTest(numbers) VALUES (1);

BEGIN TRAN
INSERT INTO LogTest(numbers) VALUES (2);

Observe that the second insert is under a BEGIN TRAN that hasn't been committed yet. Next we'll use the function [fn_dblog to check the content of the log file before taking a log backup.

SELECT [Current LSN], [Operation], [SPID], [Transaction Name], [Begin Time], [End Time] FROM fn_dblog(NULL, NULL);

On the picture we can see LSNs related to the first and second INSERT

First Query result

BACKUP LOG MyDB 
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_LOG.trn';

A log backup was taken and we can read its content with the function fn_dump_dblog using the following query:

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time] 
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_LOG.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

Second Query result

As we can see those LSNs related to the unfinished transaction actually are on the transaction log backup even though they were not committed. The catch is that as Microsoft says

The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state.

Hence after performing a restore of that log backup the data related to those unfinished transactions will be rolled back to keep the database in a consistent state. You can consider safe (can be restored) all the transactions that were committed. It can be verified by dropping the database MyDB and restoring the backups that were generated with the steps above.

6

The transaction log contains any transactions that were fully committed by the time the transaction log finishes.

To use your example above, the transaction log contains A and B. This is similar to the full backup, which contains a tail-log backup of all the transactions that were committed when the backup completes.

This site is excellent further reading. https://sqlbak.com/academy/transaction-log-backup

  • 4
    Note that the log backup may contain all the log records for Transaction C, other than the commit record. So a restore of that log backup WITH RECOVERY will first redo, and then undo Transaction C. – David Browne - Microsoft Oct 30 '19 at 13:22
  • "The transaction log contains any transactions that were fully committed by the time the transaction log finishes. " Should that be "... by the time the BACKUP finishes."? – MJH Oct 30 '19 at 13:49
  • In this case can I consider all data is being security up to the backup is finished or I consider the started point of backup? – Leonardo Lacerda Oct 30 '19 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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