SQL-Ex blog

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

Эксперимент: рост журнала по сравнению с ростом данных

На первом шаге демонстрируется пример роста журнала по сравнению с ростом файла данных при выполнении простых операций DML. База данных, используемая в эксперименте, была спроектирована таким образом, чтобы сделать очевидным воздействие шагов. Код в листинге 1 создает базу данных и включает режим полного восстановления (FULL RECOVERY)

-- Листинг 1: Создание базы с небольшим ростом файла для иллюстрации

USE [master]

GO

/* Object: Database [DB01] Script Date: 14/05/2022 9:38:51 am */CREATE DATABASE [DB01]

 CONTAINMENT = NONE

 ON PRIMARY 

( NAME = N'DB01', FILENAME = N'C:MSSQLDataDB01.mdf' , SIZE = 4MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1KB )

 LOG ON 

( NAME = N'DB01_log', FILENAME = N'E:MSSQLLogDB01_log.ldf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 1KB )

GO

ALTER DATABASE DB01 SET RECOVERY FULL;

GO

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

-- Листинг 2: Проверка роста физического журнала для базы данных

 USE DB01

 GO

 SELECT name, physical_name, size*8 , max_size 

 FROM sys.master_files

 WHERE name like 'DB01';

 GO

-- Проверка пространства файла данных базы данных

EXEC sp_spaceused; 

GO 

-- Проверка пространства файла журнала для базы данных

SELECT 

DB_NAME(database_id) [Database Name]

,total_log_size_in_bytes/1024 [Total Log Size (KB)]

,used_log_space_in_bytes/1024 [Used Log Space (KB)]

,used_log_space_in_percent [Used Log Space (]

FROM sys.dm_db_log_space_usage ;

GO

Рис.1: Размер файла данных и файла журнала, использовано (начальное состояние)

Обратите внимание на физические размеры файла данных и журнала, а также на использованное пространство, что показано на рис.1. Файлы данных и журнала имеют размеры 4096Кб и 512Кб соответственно. Напомню, что это размеры, указанные в скрипте создания базы данных. Также указано использованное на данный момент пространство.

Затем мы создаем новую таблицу в базе данных и вставляем в нее 5000 строк. После этого действия INSERT снова проверим рост файла. Результаты выполнения скрипта из листинга 2 показаны на рис.2.

-- Листинг 3: Оператор создания и наполнения таблицы

USE DB01

GO

CREATE TABLE TAB01 (

ID INT IDENTITY (1,1)

,Name CHAR(50));

GO

INSERT INTO TAB01 VALUES ('Kenneth Igiri');

GO 5000

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

Снова посмотрите внимательно на области, указанные красными стрелками на результатах. Физические размеры файла данных не изменились (4096Кб), но журнал транзакций вырос до 3840Кб. Используемое пространство увеличилось в обоих случаях, но журнал транзакций в большей мере. Это ниже иллюстрируется на диаграммах рисунков 4 и 5.

Теперь мы выполним DML из листинга 4, который представляет собой набор операторов INSERT и DELETE, которые оставляют базу данных в неизменном состоянии, что касается числа строк. Однако, как мы наблюдаем на рис.3, что имеются изменения в использовании пространства файлами данных и журнала.

-- Листинг 4: Проверка роста журнала в базе данных

 USE DB01

 GO

 INSERT INTO TAB01 VALUES ('Kenneth Igiri');

 GO 5000

 DELETE FROM TAB01 WHERE ID>5000;

 GO

SELECT COUNT(*) as Computed  FROM TAB01;

 GO

Рис.3: Размер файла данных и журнала, использовано (после вставки и удаления)

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

Замечания относительно роста журнала транзакций

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

Графический анализ

Для большей ясности возвращаемые в предыдущем разделе числа мы поместили в электронную таблицу и представили в виде гистограммы. Мы указали конкретные числа, используемые на рисунках 1, 2 и 3.

На рисунке 4 показано изменение пространства, используемого файлами данных и журнала, на одной гистограмме. Разрыв в росте используемого пространства между файлом журнала и файлом данных подтверждает более раннее утверждение. На рисунке 5 также показано, что физический журнал вносит теперь больший вклад в общий размер базы данных, чем сам файл данных.


Рис.4: Гистограмма, показывающая используемое пространство файлами данных и журнала

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


Рис.5: Гистограмма, показывающая физический размер файлов данных и журнала

Последствия роста журнала транзакций по сравнению с ростом данных

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

Некоторые администраторы пытаются срезать объемы, установив для промышленной базы данных простой режим восстановления (SIMPLE) для обслуживания роста журнала. Это чревато, потому что восстановление данных станет невозможным в случае сбоя в середине дня. Кроме того, вы по-прежнему сталкиваетесь с ростом в сценариях, когда долгоиграющие транзакции приводят к росту журнала транзакций и приводят вас к необходимости сжимать файл транзакций. Хорошей практикой является использование FULL REСOVERY MODE для промышленных баз данных.

Рекомендации

Для того, чтобы решить проблему роста журнала, вам следует принять следующие меры:

1. Проанализировать активность базы данных и определить наибольший размер журнала транзакций, до которого он может вырасти в течение самого загруженного часа дня. На этом основании предусмотрите выделенный диск, чтобы журнал транзакций никогда не превысил пространство в операционной системе (Error 9002).


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


3. Если вы используете Transaction Log Shipping для аварийного восстановления, вам может не понадобиться резервирование журнала транзакций. Однако вы должны убедиться, что ваша настройка аварийного восстановления работает стабильно.


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

Следующие разделы дают несколько больше информации о вышеперечисленных рекомендациях.

Анализ активности базы данных

Один быстрый способ отслеживать использование файла журнала транзакций на вашем экземпляре - использовать счетчик производительности Log Files Used Size (KB), что показано на рисунке 6. Этот счетчик имеется в мониторе производительности (Performance Monitor) под группой SQLServer:Databases (смотри рис.7).


Рис.6: Счетчик производительности используемых файлов журнала

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


Рис.7: Счетчик производительности используемых файлов журнала

Составьте расписание выполнения процедуры создания резервной копии журнала транзакций

Листинг 5 дает пример скрипта, который создает задание SQL Agent для создания резервной копии журнала транзакций для указанных баз данных с почасовым интервалом. Задание также посылает уведомление по завершению работы с тем, чтобы вы могли отслеживать успешное создание бэкапов журнала. Ожидается, что у вас будут другие задания для выполнения полной и дифференциальной резервных копий, если вы выберите этот метод.

Возможно такое выполнение при использовании инструментов создания резервных копий третьих фирм, таких как Veritas Netbackup, Devart's SQL Backup Tool и других. В этой статье описан очень конкретный случай использования для конфигурирования резервного копирования с помощью Veritas Netbackup.

-- Листинг 5: Создание задания для бэкапа журнала транзакций

-- Скрипт задания SQL Agent

-- Проверьте, что вы указали путь бэкапа. Это может быть также UNC-путь

-- Замените DB1, DB2, DB3 и т.д. списком баз данных экземпляра

-- Создайте оператор "DatabaseAdmin" или используйте имя по вашему выбору

USE [msdb]

GO

/* Object:  Job [Custom_Log_Backups]    Script Date: 12/11/2016 10:07:21 */BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/* Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/11/2016 10:07:21 */IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Custom_Log_Backups', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=3, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'No description available.', 

@category_name=N'[Uncategorized (Local)]', 

@notify_email_operator_name=N'DatabaseAdmin', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/* Object:  Step [Backup Log]    Script Date: 12/11/2016 10:07:22 */EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Log', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'exec sp_MSforeachdb @command1=''

DECLARE @backup sysname

set @backup=N''''L:BACKUP?_'''' + convert(nvarchar,getdate(),112)+N''''.trn''''

if ''''?'''' in ("DB1","DB2","DB3")

backup log [?] to disk = @backup with compression''', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch_Backup_Log', 

@enabled=1, 

@freq_type=4, 

@freq_interval=1, 

@freq_subday_type=8, 

@freq_subday_interval=1, 

@freq_relative_interval=0, 

@freq_recurrence_factor=0, 

@active_start_date=20161211, 

@active_end_date=99991231, 

@active_start_time=180000, 

@active_end_time=235959, 

@schedule_uid=N'575f95a5-b353-42b8-9b62-e09e0653c5b6'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Конфигурация аварийного восстановления

Конфигурирование аварийного восстановления - это вообще совершенно другая тема. Связь с настоящей статьей состоит в том, что во всех методах аварийного восстановления в SQL Server фирма Microsoft использует резервные копии журнала транзакций и усечение как часть решения. Помимо прочего, одним из фундаментальных назначений журнала транзакций является восстановление. Transaction Log Shipping реализует в фоновом режиме резервирование журналов, таким образом, нет действительной необходимости отдельно конфигурировать резервирование журналов. При использовании AlwaysOn Availability Groups резервирование журналов требуется, по крайней мере, для одной реплики.

В этой статье пошагово описывается конфигурирование Transaction Log Shipping с особым вниманием к отложенному восстановлению. Посмотрите раздел Setting Up the Environment, в котором показано, что конфигурация Log Shipping состоит из трех заданий SQL Agent Jobs - задания резервирования на первичной базе данных, задания копирования и задания восстановления (обоих на вторичных базах данных).

Резервирование журнала и сжатие

В листинге 6 показан скрипт, используемый для резервирования журнала транзакций и сжатия его до 2Мб. Обратите внимание, что требуется выполнить полный бэкап до того, как может быть выполнено резервирование журнала. Также отметим, что размер, до которого может быть сжат журнал транзакций, зависит от того, сколько доступно свободного места.

-- Листинг 6: Усечение жарнала транзакций

-- Резервирование базы данных (полный бэкап)

USE master

GO

BACKUP DATABASE DB01

TO DISK = N'E:BackupDB01.bak';

-- Резервирование журнала транзакций

BACKUP LOG DB01

TO DISK = N'E:BackupDB01_Log.trn';

-- Подтверждение свободного места в журнале транзакций

USE DB01

GO

SELECT 

DB_NAME(database_id) [Database Name]

,total_log_size_in_bytes/1024 [Total Log Size (KB)]

,used_log_space_in_bytes/1024 [Used Log Space (KB)]

,used_log_space_in_percent [Used Log Space (%)]

FROM sys.dm_db_log_space_usage ;

GO

-- Сжатие журнала транзакций до желаемого размера на основе свободного места

USE DB01

GO

DBCC SHRINKFILE ('DB01_log',2)

GO

Монторинг журнала транзакций

Microsoft предоставляет несколько инструментов для мониторинга журнала транзакций, некоторые из которых перечислены в ссылках. Инструменты третьих фирм, подобные Redgate's SQL Monitor, dbForge Transaction Log, SQL Monitor (часть Devart's DevOps tools for database) и SQL Transaction Log Reader помогают администраторам баз данных монторить и обслуживать журнал транзакций. Эти инструменты обычно имеют визуальные панели, которые облегчают работу с ними.

Заключение

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

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

Ссылки

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


2. Управление размером журнала транзакций


3. dbForge Transaction Log


4. Монитор проиводительности SQL Server


5. Инструменты Database DevOps