What Is an LDF File? The SQL Server Log That Still Contains Your Deleted Rows

7 How Do You Open an LDF File? SQL Server reads it, and two Windows viewers show it

LDF files are binary, so a text editor shows symbols and not words. Do not save it after you open one. We tested our own readers on Linux and read both pages of the two Windows tools below. We installed neither tool.

In SQL Server

SQL Server opens LDF files when the database goes online. To use a database that came as an MDF file and an LDF file, attach both. Microsoft on where attach looks for a log says it uses the old location unless you name a new one.

CREATE DATABASE shop
ON (FILENAME = '/var/opt/mssql/data/shop.mdf')
LOG ON (FILENAME = '/var/opt/mssql/data/shop_log.ldf')
FOR ATTACH;

Microsoft’s attach warning also warns against attaching or restoring databases from unknown or untrusted sources. It says to check it with DBCC CHECKDB on a nonproduction server first. Treat any LDF file from a stranger the same way.

The fn_dblog function reads the active log of the current database. Our dump of 726 records came from it. We found no Microsoft page that documents the function, so its columns can change between versions.

SELECT * FROM fn_dblog(NULL, NULL);

Two Windows viewers

These cards link to the two Windows programs that name LDF files on their own pages. Both read the log of a database and not a loose LDF file. Both pages describe a trial with limits, and we read the pages and did not use the programs.

The SysTools page describes a tool that needs the MDF file and the LDF file together. It does not open backup files that end in .trn. It exports to SQL Server, to scripts or to CSV. Its free version shows 50 records per table and cannot export.

The Freeviewer page describes the same kind of viewer with the same demo limits. We cannot say how either tool treats a damaged LDF file, because we did not use them on our 50 damaged LDF files.

In a hex view and on the command line

Four commands show what an LDF file is without opening it in SQL Server. They hash the file, print its first 16 bytes, name its type and start our 69 line script. The first three work on any Linux or macOS computer.

sha256sum shop_log.ldf
xxd -l 16 shop_log.ldf
file shop_log.ldf
python3 ldf_peek.py shop_log.ldf

The file command printed data for our LDF file. That shows only that it found no known signature. The first bytes 01 0F 00 00 are what point to a SQL Server log.

LaserDisc and Lingoes files

A LaserDisc capture opens in ld-decode on Linux, according to FileInfo and the project pages we found. A Lingoes source file opens in a text editor. We tested neither type, and we found no sample of the Lingoes type.

How the readers did on our 50 damaged LDF files

8 How Do You Check an LDF File? Six steps, and a 69 line script

The six steps

1. 1 Copy the LDF file, write down its SHA256 hash and work on the copy.
2. 2 Read the first 16 bytes with xxd -l 16 file.ldf. A SQL Server log starts with 01 0F. The letters fLaC or OggS point to a LaserDisc capture.
3. 3 Check the size. A SQL Server log is a multiple of 8,192 bytes, and ours is 1,048,576.
4. 4 Use the script below. It reads the headers, finds the blocks and counts the records and the transactions.
5. 5 Read the last lines of the output. Every problem the script finds gets its own line that starts with PROBLEM.
6. 6 If you can, compare the counts with fn_dblog on a SQL Server that has the same database.

A 69 line script that reads an LDF file

The Python script below reads an LDF file and does steps 3 to 5 in under a second. It checks the file header page and every VLF header, then reads the blocks and puts the saved sector bytes back. It counts records and prints a line for every problem. It changes no file.

import struct, sys

PAGE, SECTOR, MAXBLOCK = 8192, 512, 61440
NAMES = {1: 'FORMAT_PAGE', 2: 'INSERT_ROWS', 3: 'DELETE_ROWS', 4: 'MODIFY_ROW', 6: 'MODIFY_COLUMNS',
         7: 'SET_BITS', 0x12: 'COUNT_DELTA', 0x80: 'BEGIN_XACT', 0x81: 'COMMIT_XACT',
         0x82: 'ABORT_XACT', 0x8D: 'LOCK_XACT'}

def read_blocks(data, vpos, vsize, seq, parity, ops, problems):
    pos, end, count = vpos + PAGE, vpos + vsize, 0
    while pos + SECTOR <= end:
        n, slot_end, size = struct.unpack_from('<HHH', data, pos + 2)
        bseq, boff = struct.unpack_from('<II', data, pos + 12)
        if not ((data[pos] & 0xC0) == parity and SECTOR <= size <= MAXBLOCK and size % SECTOR == 0
                and pos + size <= end and bseq == seq and boff == (pos - vpos) // SECTOR
                and n > 0 and 24 + 2 * n <= slot_end <= size):
            break
        blk = bytearray(data[pos:pos + size])
        torn = False
        for i in range(1, size // SECTOR):
            torn = torn or (blk[i * SECTOR] & 0xC0) != parity
            blk[i * SECTOR] = blk[size - 1 - i]
        if torn:
            break
        bounds = [struct.unpack_from('<H', blk, slot_end - 2 * k)[0] for k in range(1, n + 1)]
        bounds.append(slot_end - 2 * n)
        bad = False
        for off, nxt in zip(bounds, bounds[1:]):
            if 24 <= off < nxt <= bounds[-1]:
                ops[blk[off + 22]] = ops.get(blk[off + 22], 0) + 1
            else:
                bad = True
        if bad:
            problems.append('impossible record slot in the block at offset %d' % pos)
        count += 1
        pos += size
    if pos < end and set(data[pos:end]) != {0xC0}:
        problems.append('block or unused space is damaged at offset %d' % pos)
    return count

def peek(path):
    data, ops, problems = open(path, 'rb').read(), {}, []
    if len(data) < 2 * PAGE or data[:2] != b'\x01\x0f':
        problems.append('no file header page at the start')
    pos, vlfs = PAGE, []
    while pos + PAGE <= len(data):
        mark, parity, _, seq = struct.unpack_from('<BBHI', data, pos)
        size, start = struct.unpack_from('<qq', data, pos + 16)
        if mark != 0xAB or size < 2 * PAGE or pos + size > len(data) or start != pos:
            problems.append('bad VLF header at offset %d' % pos)
            break
        used = seq != 0 and parity in (0x40, 0x80)
        if not used and set(data[pos + PAGE:pos + size]) != {0xC0}:
            problems.append('VLF at offset %d looks unused but contains data' % pos)
        vlfs.append((pos, size, seq, parity, read_blocks(data, pos, size, seq, parity, ops, problems) if used else None))
        pos += size
    if pos < len(data) and not problems:
        problems.append('%d extra bytes after the last VLF' % (len(data) - pos))
    return vlfs, ops, problems

if __name__ == '__main__':
    vlfs, ops, problems = peek(sys.argv[1])
    for i, (pos, size, seq, parity, blocks) in enumerate(vlfs, 1):
        state = 'unused' if blocks is None else 'sequence %d, %d blocks' % (seq, blocks)
        print('VLF %d at %d, %d bytes, %s' % (i, pos, size, state))
    print('records', sum(ops.values()))
    print('transactions %d begun, %d committed, %d aborted' % (ops.get(0x80, 0), ops.get(0x81, 0), ops.get(0x82, 0)))
    for n, code in sorted(((n, c) for c, n in ops.items()), reverse=True)[:5]:
        print('  %-14s %d' % (NAMES.get(code, '0x%02X' % code), n))
    print('\n'.join('PROBLEM ' + p for p in problems) or 'no problems found')

On our healthy LDF file it prints four VLFs, 726 records and 35 begun transactions. On damaged LDF files it names the point where it stopped. For example, it reads 12 records in copy 17 and says the first VLF looks unused but contains data.

python3 ldf_peek.py shop_log.ldf
VLF 1 at 8192, 253952 bytes, sequence 42, 37 blocks
VLF 2 at 262144, 253952 bytes, sequence 43, 3 blocks
VLF 3 at 516096, 253952 bytes, unused
VLF 4 at 770048, 278528 bytes, unused
records 726
transactions 35 begun, 34 committed, 1 aborted
  COUNT_DELTA    233
  INSERT_ROWS    137
  SET_BITS       61
  MODIFY_ROW     49
  LOCK_XACT      43
no problems found

python3 ldf_peek.py copy05.ldf
records 0
transactions 0 begun, 0 committed, 0 aborted
PROBLEM bad VLF header at offset 8192

python3 ldf_peek.py copy17.ldf
VLF 1 at 8192, 253952 bytes, unused
records 12
PROBLEM VLF at offset 8192 looks unused but contains data

python3 ldf_peek.py copy33.ldf
VLF 1 at 8192, 253952 bytes, sequence 42, 11 blocks
records 299
PROBLEM block or unused space is damaged at offset 106496

python3 ldf_peek.py copy24.ldf
records 725
PROBLEM impossible record slot in the block at offset 16384

What the script cannot see

The script reads the structure of LDF files and cannot say if the values are the ones SQL Server wrote. It printed no problem for copy 38, where we changed the name Ali to Bob. The same is true for copies 37 to 42 and 46, which changed bytes inside records.

python3 ldf_peek.py copy38.ldf
records 726
no problems found

The script never executes an LDF file, and it does not decode what the records say. It also cannot name the SQL Server version that wrote the file. We wrote it from one file and from the code of three open source readers.

Want the causes of an error? Go to the causes.

9 Is an LDF File Safe to Share? It contains old rows, so treat it like the database

Opening an LDF file in a hex view is not the risk. The risk is what LDF files contain and who gets a copy. We scanned no file for malware, so we cannot say what a downloaded LDF file contains. We can say what LDF files can store.

What an LDF file can contain

What Microsoft says about attaching

Microsoft’s advice on unknown databases says not to attach or restore databases from unknown or untrusted sources. Such a database can contain malicious code that executes unintended Transact-SQL. It advises you to use DBCC CHECKDB on a nonproduction server and to read its stored procedures first.

The page adds that attach sets some security options off by default. Those include is_trustworthy_on. It is safer to read an untrusted LDF file in a viewer and not to attach it.

Before you share an LDF file

1. 1 Ask who needs it. A person who asks for the MDF file does not always need the LDF file.
2. 2 Treat the LDF file as sensitive as the database, and send it only over the channel you use for the data.
3. 3 Hash the file and send the hash on another channel, so the other side can check it.
4. 4 Do not assume a backup, a log truncation or a shrink erased old rows from an LDF file. We did not test them.
5. 5 Look at the LDF file in our script first, to see how many records and transactions it contains.

11 How Do You Shrink, Move or Rebuild an LDF File? What Microsoft says, since we could not test SQL Server

This part reports what Microsoft says about LDF files, because we had no SQL Server to test. We read the pages named in every paragraph and did not change any database. Our own tests cover the file and not the server.

How SQL Server makes an LDF file

Microsoft on CREATE DATABASE says it makes one log file when you name none. Its size is 25 percent of the data files or 512 KB, whichever is larger. The log guide says growth under 64 MB makes 4 VLFs, 64 MB to 1 GB makes 8 and more than 1 GB makes 16.

The same guide says SQL Server 2022 makes one VLF for growth of 64 MB or less. Our LDF file has four. Microsoft on log growth says the default growth is 64 MB from SQL Server 2016, and it advises you to keep FILEGROWTH at or below 1,024 MB.

Shrink an LDF file

A shrink returns free space to the operating system. Microsoft on shrinking says it should not be a regular maintenance task, and it works only when the database is online and at least one VLF is free. AUTO_SHRINK is false by default, and Microsoft does not recommend turning it on.

A log that keeps growing has a reason. Query log_reuse_wait_desc in sys.databases to see it. The values include NOTHING, CHECKPOINT, LOG_BACKUP, ACTIVE_TRANSACTION and REPLICATION. Fix the reason before you shrink.

SELECT name, log_reuse_wait_desc
FROM sys.databases;

Move or copy a database with its log

To move a database, detach it and attach it again with both files. Microsoft on detaching says to keep every detached log file until the attach works without them. Copy the files first and hash them, so you can compare them after the move.

Rebuild a lost log

Microsoft on rebuilding a log says FOR ATTACH_REBUILD_LOG makes a new 1 MB log in the default log location. It needs an orderly shutdown and all the data files, and it works only for read and write databases.

It also breaks the log backup chain, and Microsoft advises a full backup straight after. Rebuilding drops the old LDF file, so it drops every record we studied. Use it only when no better copy exists.

Convert an LDF file to another format