SQL Server 2005, огромный файл LDF

Резюме: Если вы оказались в такой ситуации, вы, вероятно, захотите вариант 1 ниже. Пропустите там, чтобы решить проблему, или читайте дальше для более подробного объяснения.

База данных в Microsoft SQL Server имеет различные "режимы восстановления", в которых она может быть настроена:

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

   План резервного копирования для такой базы данных может быть "Полное резервное копирование каждое воскресенье плюс ежедневное разностное резервное копирование". Это позволяет сохранять относительно небольшие разностные резервные копии, поскольку они содержат только данные, измененные с предыдущего воскресенья. В случае сбоя вы теряете данные не более чем за 1 день.

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

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

3. Массовая регистрация. Это что-то вроде "режима полного восстановления, кроме случаев, когда я говорю иначе".

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

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

Для получения дополнительной информации о том, как работает журнал транзакций, пожалуйста, прочитайте отличные статьи Пола Рэндала:

• Понимание ведения журнала и восстановления в SQL Server
• Важность правильного управления размером журнала транзакций
• Неправильные представления о журнале и резервном копировании журнала: как убедить себя

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

Вариант 1: перейти на простую модель восстановления

Возможно, вам не нужно делать резервные копии журналов (поскольку вы не делаете это сейчас), поэтому вы можете просто переключиться на простую модель восстановления:

ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE

Это пометит все в журнале транзакций как усеченное, поэтому файл журнала не станет больше. Как только вы это сделаете, чтобы сжать физический файл журнала:

DBCC SHRINKFILE N'MyDatabase.ldf'

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

Вариант 2. Начните делать резервные копии журнала транзакций

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

Во-первых, чтобы выйти из плохой ситуации, вы хотите укоротить весь журнал, а затем сжать его:

BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE N'MyDatabase.ldf'
GO

Теперь ваш файл журнала транзакций должен иметь разумный размер. Он снова начнет расти снова; хоть. Теперь вы должны начать регулярно делать резервные копии журналов. Это можно сделать с помощью плана обслуживания или регулярного запуска такой команды, как:

BACKUP LOG [MyDatabase] TO DISK = N'C:\Backups\MyDatabase_Log_Backup1.bak' WITH INIT

Примечание

DBCC SHRINKFILE не предназначен для регулярного использования:

• Файлы журналов будут расти до стабильного размера в зависимости от активности базы данных и частоты выполнения резервного копирования журналов.
• Сжатие файлов данных полностью фрагментирует ваши индексы.

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