Чтение журнала транзакций SQL Server

Журнал транзакций SQL Server содержит подробную информацию обо всех операциях, совершённых в базе данных. Этой информации достаточно, чтобы восстановить базу данных на определённый момент времени, повторно воспроизвести все операции над данными или отменить их. Но как просмотреть эту информацию, найти конкретную транзакцию в журнале, определить, что именно происходило в базе и откатить какие-нибудь изменения, например, восстановить случайно удалённые записи?

Разобраться в той информации, которая хранится в журнале транзакций или в резервной копии журнала транзакций не так просто.

Если открыть файл журнала транзакций *.LDF или файл резервной копии журнала *.TRN в любом двоичном редакторе, то информация, которую вы увидите, будет мало чем информативна. Ниже представлен фрагмент LDF-файла:

Функция fn_dbblog

fn_dblog – это недокументированная функция SQL Server, которая позволяет просматривать активную часть журнала транзакций в режиме реального времени.

Давайте посмотрим, как с ней работать:

1. Выполните функцию fn_dblog

Select * FROM sys.fn_dblog(NULL,NULL)



Функция возвращает 129 столбцов, поэтому желательно сузить результирующий набор по необходимым наборам полей и по возможности ограничить выборку только нужным типом транзакций

2. Из всего набора данных, который возвращает функция fn_dblog выведем только нужные транзакции.

Например, выберем только транзакции на вставку строк в таблицу:

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');



Чтобы увидеть транзакции на удаление строк, выполните следующий скрипт:

SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');



3. Информация по вставленным или удалённым строкам храниться в столбцах – RowLog Contents 0, RowLog Contents 1, RowLog Contents 2, RowLog Contents 3, RowLog Contents 4, Description и Log Record

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

Вставленные и удаленные строки хранятся в шестнадцатеричных значениях. Для того, чтобы вытащить данные из этих значений вы должны знать формат хранения, понимать биты состояний, знать общее количество столбцов и так далее.

4. Далее необходимо преобразовать двоичные данные в табличный вид с учётом типа данных столбцов таблицы. Следует отметить, что механизм преобразования различный для разных типов данных.

fn_dbLog замечательный бесплатный инструмент для чтения журнала транзакций, но эта функция имеет ряд ограничений – разобраться в данных достаточно сложно, т.к. среди прочей информации содержатся записи, связанные с системными таблицами, функция отображает только активную часть журнала и не отображает информацию по обновлению BLOB-значений.

Операция UPDATE при минимальном протоколировании журнала транзакций не содержит полное значение, которое было до и после изменений, а хранит только то, что изменилось (SQL Server может записать, что изменилось значение “G” на “D”, хотя в действительности изменилось слово “GLOAT” на “FLOAT”). В этом случаи вам потребуется вручную восстанавливать все промежуточные состояния записи на странице от первой её вставки до момента, который вас интересует.

При удалении BLOB-объектов сами объекты не записываются в журнал, а лишь фиксируется факт удаления. Для восстановления, удалённого BLOB-объекта вам необходимо найти в журнале пару для этого удаления, которой является ранее осуществлённая вставка, а она скорее всего уже не содержится в активной части журнала.

Функция fn_dump_dblog

fn_dump_dblog – это ещё одна недокументированная функция, которая позволяет просматривать журнал транзакций из резервной копии журнала транзакций, как сжатого, так и обычного.

1. Ниже пример запуска функции fn_dump_dblog, обратите внимание, что необходимо указать все её 63 параметра

SELECT *
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.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);



Т.к. функция fn_dump_dblog возвращает так же, как и fn_dblog 129 столбцов, то желательно сократить этот набор полей

SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
     [transaction name],
       Description
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.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);

Но вам потребуется опять расшифровать шестнадцатеричные значения, чтобы найти искомые записи



И вы опять получаете те же самые ограничения, что и при работе с функцией fn_dblog.

Для восстановления БД из копии журнала транзакций до определённого момента времени или до конкретной транзакции, вам необходимо:

2. Определить LSN (Log Sequence Number) для этой транзакции
3. Преобразовать LSN в формат, который используется в конструкции WITH STOPBEFOREMARK = ‘<mark_name>’, например значение 00000070:00000011:0001 должно быть переведено в формат 112000000001700001
4. Восстановите полную резервную копию БД и всю цепочку резервных копий журнала транзакций до нужной транзакции с помощью конструкции WITH STOPBEFOREMARK = ‘<mark_name>’ , где укажите идентификатор нужной транзакции.

   RESTORE LOG AdventureWorks2012
   FROM
       DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
   WITH
       STOPBEFOREMARK = 'lsn:112000000001700001',
       NORECOVERY;

DBCC PAGE

Ещё одна полезная команда DBCC PAGE, но также, как и две предыдущих функции –недокументированная. Она позволяет просматривать содержимое файлов MDF и LDF. Её синтаксис:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Для просмотра содержимого первой страницы журнала транзакций БД AdventureWorks2012, необходимо выполнить:

SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

В качестве результата вы получите сообщение:

DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

По умолчанию результат команды DBCC PAGE не выводится в SQL Server Management Studio и для её отображения первым шагом необходимо включить флаг трассировки 3604:

DBCC TRACEON (3604, -1)

И теперь повторно выполните команду:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Вы увидите несколько ошибок и заголовок страницы, которые можно проигнорировать. Ниже вы получите шестнадцатеричное отображение LDF-файла:

Полученный результат ничем не отличается от того, который вы можете получить в любом hex-редакторе, а может быть даже и в менее наглядном виде. Главное отличие – это возможность просматривать файл в режиме реального времени, без отключения БД, но дружелюбным такой формат никак нельзя назвать.

Use ApexSQL Log

ApexSQL Log – это инструмент, который позволяет работать с журналом транзакций SQL Server в наглядном виде. Он позволяет просматривать текущий журнал транзакций в режиме реального времени, обращаться к резервным копиям журнала транзакций, как обычным, так и созданных в режиме компрессии. При этом приложение самостоятельно считывает данные из резервных копий БД, чтобы получить всю необходимую информацию для успешного восстановления. С помощью ApexSQL Log вы можете просматривать цепочки транзакций, которые произошли в вашей БД, даже те, которые были совершены до установки утилиты. В отличии от недокументированных и неподдерживаемых функций, рассмотренных выше, вы получите наглядную информацию о том, какие операции происходили над объектами, сможете увидеть старое и новое значение.

1. Запустите ApexSQL Log

2. Подключитесь к базе данных, чей журнал транзакций вы хотите проанализировать

   

3. На шаге Select SQL logs to analyze, выберите записи, которые нужно прочитать. Убедитесь, что они образуют полную цепочку

   

4. Чтобы добавить резервные копии журнала транзакций и отдельные файлы LDF, используйте кнопку Add

5. Используйте фильтр на шаге Filter setup, чтобы уменьшить количество считываемых транзакций с помощью указания временного диапазона, типа операций, таблицы и другие фильтры

   

6. Нажмите Open

   Полный результат можно будет увидеть в табличном виде

   Вы сможете отследить, когда операция началась и когда закончилась, тип операции, схему и объект, над которым произошла операция, имя пользователя, совершившего эту операцию, а также имя компьютера и приложения из которого эта операция была совершена. Для операций обновления (UPDATE) вы сможете увидеть, как новое, так и старое значение.

   

Чтобы избежать нечитаемых шестнадцатеричных значений, недокументированных функций, непонятного содержимого колонок, запросов со сложной конструкцией, сложных сценариев получения данных, неполных данных операций UPDATE, а также проблем с получением BLOB значений из журнала транзакций SQL Server, используйте программу ApexSQL Log. Она за вас выполнит все сложные операции и предоставит результат в читабельном виде. Кроме того, она позволит вам с помощью одного нажатия отменить или повторно выполнить нужную транзакцию.

Переводчик: Алексей Князев