Как контролировать размер файла журнала базы данных SQL Server?

Question

Мы используем MSSQL Server, и наше приложение ежедневно осуществляет интенсивный обмен данными; ок. 20 000 строк новых данных в день, и база данных "Размер файла журнала" также продолжает расти. Мы удаляем данные после обработки, чтобы держать размер БД под контролем, однако "Файл журнала БД" продолжает увеличиваться.

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

Есть ли постоянное исправление для автоматического контроля размера файла журнала БД? Допустим, мы хотим выделить максимум 4 ГБ. Это должно автоматически стереть старые журналы.

USE db1;
GO

ALTER DATABASE db1
SET RECOVERY SIMPLE;
GO
--first parameter is log file name and second is size in MB
DBCC SHRINKFILE (db1_log, 999);

ALTER DATABASE db1
SET RECOVERY FULL;
GO

С уважением, Радж

Answer 1

Я вижу это:

SET RECOVERY FULL;

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

Есть ли постоянное исправление для автоматического контроля размера файла журнала БД?

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

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

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

Answer 2

Есть два способа очистки t-журнала в SQL Server:

1. база данных с использованием модели восстановления SIMPLE - при возникновении контрольной точки журнал очищается

2. базы данных с использованием модели восстановления FULL или BULK_LOGGED - при создании резервной копии журнала

Единственный раз, когда t-log будет уменьшаться, если вы выполните SHRINKFILE.

Вы можете предварительно изменить размер файла журнала до 4 ГБ и автоматически увеличить до 4 ГБ.

USE [master]
GO
ALTER DATABASE [DB] MODIFY FILE ( NAME = N'DB_log', SIZE = 4096000KB , FILEGROWTH = 4096000KB )
GO

Ваш рост файла должен быть установлен на In Megabytes и не In Percent,

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

Если вы можете терпеть 1 день простоя / потери данных, простое восстановление будет работать для вас. Но если у вас есть магазин, работающий круглосуточно и без выходных, и вам необходимо восстановить данные на определенный момент времени, вам нужно установить полное восстановление базы данных (резервные копии журналов, необходимые для очистки ваших t-журналов) и спросить вас о требованиях RPO/RTO.