Журнал транзакций SQL

  • автор:

Ошибка СУБД:
Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных «zup» заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1
или
Ошибка СУБД:
Microsoft OLE Provider for SQL Server: The transaction log for database «DataBase” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002
Решение:
1. Посмотрите сколько свободного места осталось на дисках, может его нет и логу некуда записаться…
2. Это ошибка Microsoft SQL Server — переполняется лог транзакций и не очищается. Урезать его возможно различными способами, в том числе и с помощью стандартной оснастки, но не всегда данная операция получается, и размер файла лога остается прежним. Как вариант предлагаю следующее решение из двух строчек:
для использования убрать символ _

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

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

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

Функция fn_dbblog

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

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

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

    2. Select * FROM sys.fn_dblog(NULL,NULL)

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

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

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

    SELECT , Operation, Context, , FROM sys.fn_dblog (NULL, NULL) WHERE operation IN (‘LOP_INSERT_ROWS’);

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

    SELECT , , , 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. Для каждого типа транзакций используются разные столбцы, для того, чтобы получить нужную вам информацию вы должны точно знать какие столбцы используются для каких транзакций, а сделать это не просто, так, как официальной документации с описанием нет.

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

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

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

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

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

Функция fn_dump_dblog

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

DBCC PAGE

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

DBCC PAGE ( {‘dbname’ | dbid}, filenum, pagenum )

Для просмотра содержимого первой страницы журнала транзакций БД 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. Она за вас выполнит все сложные операции и предоставит результат в читабельном виде. Кроме того, она позволит вам с помощью одного нажатия отменить или повторно выполнить нужную транзакцию.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *