During a log backup is the data backed up to the start or end of the operation?

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?

• Transaction A commits

• Transaction B opens

• Log backup begins

• Transaction C opens

• Transaction B commits

• Log backup completes

• Transaction C commits

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

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

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 ( see Recovery and the transaction log).

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.

StackExchange Database Administrators Q#252191, answer score: 23