Усечение журнала транзакций и уменьшение размера файла журнала для SQL Server

Acronis Backup не выполняет усечение журналов транзакций после создания резервной копии диска. Если вы не используете собственный механизм резервного копирования Microsoft SQL Server или любое решение стороннего производителя для резервного копирования, которое автоматически управляет журналами транзакций, можно управлять журналами следующими методами.

• Усечение журнала транзакций. При усечении журнала высвобождаются неактивные виртуальные файлы журнала (содержащие только неактивные записи журнала) для повторного использования новыми записями журнала. Усечение может предотвратить рост физического файла журнала, но не уменьшает его размер.

  Дополнительные сведения об усечении см. в следующей статье:
  http://technet.microsoft.com/ru-ru/library/ms189085(v=sql.105)

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

  Дополнительные сведения об уменьшении размера см. в следующей статье:
  http://technet.microsoft.com/ru-ru/library/ms178037(v=sql.105)

Усечение журнала с помощью SQL Server Management Studio

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

1. Как переключить базу данных на простую модель восстановления
   1. Запустите среду Microsoft SQL Server Management Studio и подключитесь к соответствующему экземпляру.
   2. Щелкните базу данных правой кнопкой мыши и выберите пункт Свойства. Откроется диалоговое окно Свойства базы данных.
   3. В области Выберите страницу нажмите Параметры.
   4. В списке Модель восстановления выберите модель Простая.
2. Файлы журнала транзакций будут усекаться автоматически.
3. Переключите базу данных обратно на полную модель восстановления (Full) или модель восстановления с неполным протоколированием (Bulk-logged) таким же способом, как в шаге 1.

Автоматизация усечения журнала и уменьшения его размера 

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

Дополнительные сведения о языке Transact-SQL и утилите sqlcmd см. в следующих статьях:

• Использование Transact-SQL: http://technet.microsoft.com/ru-ru/library/ms189826(v=sql.90)
• Использование утилиты sqlcmd: http://technet.microsoft.com/ru-ru/library/ms170572(SQL.90).aspx

Как автоматизировать усечение журнала транзакций и уменьшение его размера для одного экземпляра SQL

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

   USE database_name
   ALTER DATABASE имя_базы_данных SET RECOVERY SIMPLE;
   DBCC SHRINKFILE(имя_файла_журнала);
   ALTER DATABASE имя_базы_данных SET RECOVERY FULL;

   В последней строке значение SET RECOVERY зависит от исходной модели восстановления конкретной базы данных и может быть FULL или BULK_LOGGED.

   Пример экземпляра с двумя базами данных (TestDB1 и TestDB2):

   USE TestDB1;
   ALTER DATABASE TestDB1 SET RECOVERY SIMPLE;
   DBCC SHRINKFILE(TestDB1_log);
   ALTER DATABASE TestDB1 SET RECOVERY FULL;
   
   USE TestDB2;
   ALTER DATABASE TestDB2 SET RECOVERY SIMPLE;
   DBCC SHRINKFILE(TestDB2_log);
   ALTER DATABASE TestDB2 SET RECOVERY BULK_LOGGED;

2. Добавьте следующую команду sqlcmd к команде, выполняемой после резервного копирования:

   sqlcmd -S myServer\instanceName -i C:\myScript.sql

   Куда:

   • myServer — имя сервера;
   • instanceName — имя экземпляра;
   • C:\myScript.sql — путь к файлу сценария, созданному в шаге 1.

Как автоматизировать усечение журнала транзакций и уменьшение его размера для нескольких экземпляров SQL

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

1. Создайте отдельный файл сценария для каждого экземпляра (например, C:\script1.sql и C:\script2.sql).
2. Создайте пакетный файл (например, C:\truncate.bat), который будет содержать команды для соответствующего экземпляра:

   sqlcmd -S myServer\instance1 -i C:\script1.sql
   sqlcmd -S myServer\instance2 -i C:\script2.sql

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