Исследуем fn_dblog(): недокументированная функция SQL Server для чтения журнала транзакций

Автор: SQLYARD, Exploring fn_dblog(): The Undocumented SQL Server Function That Reads the Transaction Log

SQL Server ведёт детальную запись каждого изменения в журнале транзакций. Обычно вы взаимодействуете с ним косвенно — через резервные копии, репликацию или восстановление. Но существует скрытая, недокументированная функция fn_dblog(), которая позволяет напрямую запрашивать активный журнал транзакций.

Эта функция может помочь вам:

• Расследовать удаления и изменения данных.
• Отслеживать активность DDL и DML.
• Устранять проблемы вроде случайной потери данных.
• Анализировать поведение репликации и восстановления.

Поскольку функция недокументирована, Microsoft её официально не поддерживает и она может измениться без предупреждения. Тем не менее, для администраторов БД и специалистов по расследованию инцидентов это мощный инструмент.

Как работает fn_dblog()

fn_dblog() раскрывает содержимое активной части журнала транзакций. Базовый синтаксис таков:

SELECT *
FROM fn_dblog(NULL, NULL);

• Два параметра NULL указывают SQL Server вывести всё, что сейчас находится в активном журнале.
• Можно также передавать LSN (Log Sequence Number), чтобы ограничить диапазон, но для расследований NULL обычно достаточно.

Важно: fn_dblog() работает только с активным журналом. Если журнал был сохранён и усекается (truncated), вы не увидите старые транзакции.

Вывод fn_dblog() содержит десятки столбцов (в зависимости от версии около 130+), но некоторые особенно полезны:

Столбец	Описание
Operation	Тип операции журнала (например, LOP_INSERT_ROWS, LOP_DELETE_ROWS, LOP_BEGIN_XACT).
Transaction ID	Идентификатор транзакции. Можно группировать по нему, чтобы увидеть всю транзакцию целиком.
Context	Контекст операции (например, LCX_HEAP, LCX_CLUSTERED).
AllocUnitName	Показывает таблицу или индекс, к которым относится операция.
Transaction Name	Может включать системные операции вроде CREATE INDEX, DROPOBJ или пользовательские транзакции.
Begin Time / End Time	Метки времени начала и окончания транзакции.
SPID	Сеанс, сгенерировавший транзакцию.

Пример запроса для получения данных для конкретной таблицы:

SELECT [Current LSN], Operation, Context, Transaction_ID, AllocUnitName, [Transaction Name]
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE '%YourTableName%';

Поиск удалённых данных

Если вы расследуете случайные удаления, fn_dblog() поможет определить кто, что и когда удалил.

SELECT [Current LSN], Operation, Context, AllocUnitName, [Transaction Name], [Begin Time], [End Time], [SPID]
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS';

Хотя fn_dblog() не возвращает напрямую данные удалённой строки, он предоставляет:

• Подробности транзакции
• Задействованную таблицу
• Когда произошло удаление
• Кто это сделал (SPID)

Это поможет вам совместить информацию с восстановлением «до точки во времени» (PITR) или сторонними инструментами для возврата данных.

Исследование активности DDL и DML

Вы также можете отследить:

• Удаления таблиц (DROPOBJ)
• Перестроения индексов
• Массовые вставки
• Обновления

Пример:

SELECT [Current LSN], Operation, Transaction_ID, AllocUnitName, [Transaction Name]
FROM fn_dblog(NULL, NULL)
WHERE Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DROP_OBJECT');

Это даёт быстрый обзор того, что происходит в базе на уровне журнала.

Фильтрация по транзакции

Если вы нашли подозрительную транзакцию, возьмите её Transaction_ID и отфильтруйте детали:

SELECT *
FROM fn_dblog(NULL, NULL)
WHERE Transaction_ID = '0000:00001234';

Так вы увидите все записи журнала в рамках этой транзакции и сможете пошагово реконструировать её ход.

Осторожно: недокументировано — значит, не поддерживается

• Microsoft не документирует и не поддерживает fn_dblog().
• Поведение может меняться между версиями SQL Server.
• Для запуска требуются права sysadmin.
• Функция работает только c онлайн‑базами, не с резервными копиями или отсоединёнными файлами журнала (для этого есть fn_dump_dblog()).

Продвинутый пример: отслеживание удалений в реальном времени

Можно обернуть fn_dblog() в задание или процедуру мониторинга для обнаружения удалений:

CREATE TABLE dbo.LogMonitor
(
    LSN NVARCHAR(50),
    Operation NVARCHAR(50),
    AllocUnitName NVARCHAR(255),
    TranName NVARCHAR(255),
    BeginTime DATETIME,
    EndTime DATETIME
);
INSERT INTO dbo.LogMonitor
SELECT [Current LSN], Operation, AllocUnitName, [Transaction Name], [Begin Time], [End Time]
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS';

После этого можно настраивать оповещения на основе этой таблицы с помощью SQL Agent или собственного мониторинга. Однако, не забывайте, что любая дополнительная нагрузка на журнал транзакций может привести к увеличению времени фиксации транзакций, что чтревато неприятными последствиями.

Практикум для начинающих администраторов: знакомство с fn_dblog()

Небольшая лабораторная работа поможет освоиться пошагово:

1. Создайте тестовую таблицу

   CREATE TABLE dbo.TestLog
   (
       ID INT IDENTITY(1,1),
       DataValue NVARCHAR(100)
   );

2. Вставьте пример данных

   INSERT INTO dbo.TestLog (DataValue) VALUES ('First'), ('Second'), ('Third');

3. Удалите одну строку

   DELETE FROM dbo.TestLog WHERE ID = 2;

4. Запустите fn_dblog()

   SELECT [Current LSN], Operation, AllocUnitName, [Transaction Name], [Begin Time], [End Time]
   FROM fn_dblog(NULL, NULL)
   WHERE AllocUnitName LIKE '%TestLog%';

   Вы увидите LOP_DELETE_ROWS для удалённой записи и LOP_INSERT_ROWS для вставок.

5. Определите Transaction ID и повторно выполните запрос, отфильтровав по нему, чтобы увидеть все записи журнала в рамках этой транзакции.