Заблуждения относительно журнала и резервных копий журнала: как самому проверить

Автор: Paul Randal, Misconceptions around the log and log backups: how to convince yourself

До сих пор широко распространено заблуждение, что при корректной работе в моделях восстановления FULL или BULK_LOGGED полные или дифференциальные резервные копии могут усекать журнал. Нет. Это НИКОГДА не происходит. Это одна из причин, по которой я посвящаю целый доклад этой теме на конференции PASS в этом году — поведение журнала транзакций, по моему скромному мнению, является одной из самых неправильно понимаемых частей SQL Server.

Обратите внимание, я сказал «при корректной работе в моделях восстановления FULL или BULK_LOGGED». Если вы переключаете модель восстановления на FULL или BULK_LOGGED, то до тех пор, пока не создадите первую полную резервную копию, вы по сути остаётесь в модели восстановления SIMPLE, и журнал будет усекаться при контрольной точке. Как только вы создадите эту первую полную резервную копию, вы перейдёте в режим «я буду управлять размером журнала через резервные копии журнала». После этого ЕДИНСТВЕННОЕ, что позволит очистить/усечь журнал, — это резервная копия журнала, при условии, что больше ничто не требует наличия этих записей журнала транзакций.

Если вы не знакомы с терминами «очистка журнала» или «усечение журнала», они означают одно и то же — часть журнала транзакций помечается как больше не нужная и может быть перезаписана. Ничего не обнуляется, размер файла журнала не изменяется. Некоторая справочная информация по этому вопросу:

• Моя статья Ведение журнала и восстановление в SQL Server в TechNet Magazine за февраль 2009 года. В ней также есть видеодемонстрация, показывающая, что журнал продолжает усекаться при контрольной точке после переключения в FULL и до создания полной резервной копии.
• Редакционная статья, которую я написал в апреле, о важности правильного управления размером журнала транзакций.
• Довольно глубокая статья в блоге, которую я написал об анализе последовательностей VLF в журнале: Поговорим ещё о циклической природе журнала транзакций.

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

Есть очень простой ответ: вы не можете.

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

Один из моментов в обсуждении заключался в том, что если вы работаете в моделях восстановления FULL или BULK_LOGGED и делаете полную резервную копию после операции обслуживания, но до резервной копии журнала, то полная резервная копия будет содержать все изменения, внесённые операцией обслуживания, да, и очистит журнал.

Нет. Никогда.

Резервная копия журнала — это ВЕСЬ журнал, сгенерированный с момента последней резервной копии журнала. Если бы это было не так, как бы работала доставка журналов? Вы могли бы сделать полную резервную копию на основном сервере доставки журналов и внезапно разорвать цепочку резервных копий журнала, и доставка журналов сломается. Нет, это не так работает. Полная резервная копия содержит только тот объём журнала транзакций, который необходим для восстановления этой базы данных до согласованного на момент времени состояния — момента завершения этапа чтения данных при создании полной резервной копии. Я подробно писал об этом ранее:

• Развенчание пары мифов о полных резервных копиях базы данных (которая также объясняет, почему существует синтаксис WITH STOPAT для полных и дифференциальных резервных копий, но он не имеет эффекта и присутствует только для того, чтобы позволить вам использовать WITH STOPAT для всех резервных копий в последовательности восстановления на момент времени).
• Какая часть журнала транзакций попадает в FULL BACKUP.

Но вы не обязаны верить мне на слово — убедиться в этом очень просто. Следующий скрипт покажет вам, что полная резервная копия не влияет на журнал транзакций. Он выполняет следующее:

1. Создаёт базу данных и переводит её в модель восстановления FULL, с созданием полной резервной копии.
2. Создаёт и заполняет таблицу с индексом.
3. Создаёт резервную копию журнала 1 (просто для очистки).
4. Перестраивает индекс.
5. Создаёт резервную копию журнала 2.
6. Перестраивает индекс.
7. Создаёт полную резервную копию.
8. Создаёт резервную копию журнала 3.

И мы увидим, что резервная копия журнала №3 будет того же размера, что и резервная копия журнала №2. Полная резервная копия не окажет никакого влияния.

Вот скрипт:

USE master;
GO
DROP DATABASE LogBackupTest;
GO
CREATE DATABASE LogBackupTest;
GO
USE LogBackupTest;
GO
 
ALTER DATABASE LogBackupTest SET RECOVERY FULL;
GO
BACKUP DATABASE LogBackupTest TO
DISK = 'C:SQLskillsLogBackupTest_Full1.bak' WITH INIT;
GO
 
CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
GO
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO
SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 1000
 
BACKUP LOG LogBackupTest TO
DISK = 'C:SQLskillsLogBackupTest_Log1.bak' WITH INIT;
GO
 
-- Перестроить индекс, чтобы сгенерировать журнал и получить базовый уровень
ALTER INDEX t1c1 ON t1 REBUILD;
GO
BACKUP LOG LogBackupTest TO
DISK = 'C:SQLskillsLogBackupTest_Log2.bak' WITH INIT;
GO
 
-- Теперь повторим, но сделаем полную резервную копию перед резервной копией журнала
ALTER INDEX t1c1 ON t1 REBUILD;
GO
BACKUP DATABASE LogBackupTest TO
DISK = 'C:SQLskillsLogBackupTest_Full2.bak' WITH INIT;
GO
BACKUP LOG LogBackupTest TO
DISK = 'C:SQLskillsLogBackupTest_Log3.bak' WITH INIT;
GO
    

А вот результат:

Резервная копия журнала №3 того же размера, что и резервная копия журнала №2. Она содержит весь журнал, сгенерированный с момента создания резервной копии журнала №2. Полная резервная копия не оказала никакого влияния, потому что это разорвало бы цепочку резервных копий журнала.

Если вы не верите мне, запустите скрипт сами и увидите. Полная резервная копия не влияет и не может влиять на журнал транзакций.