Почему слетает модель восстановления базы данных MS SQL Server с FULL на SIMPLE и как это исправить?

← На главную

При администрировании баз данных 1С:Предприятия на платформе MS SQL Server специалисты иногда сталкиваются с крайне неприятной проблемой: модель восстановления базы данных (Recovery Model) самопроизвольно переключается с Full (Полная) на Simple (Простая). Это критически опасная ситуация для сохранности данных. При переходе в режим SIMPLE немедленно прерывается цепочка последовательных номеров лога (LSN chain), что делает невозможным создание резервных копий транзакционного журнала и восстановление базы данных на определенный момент времени (Point-in-Time Recovery).

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

Суть проблемы: опасный антипаттерн обслуживания

Основная причина, по которой база данных неожиданно оказывается в модели SIMPLE, кроется в некорректно настроенных регламентных процедурах обслуживания. Очень часто начинающие администраторы баз данных (DBA) или автоматизированные скрипты пытаются бороться с неконтролируемым ростом файла журнала транзакций (.ldf) с помощью следующей последовательности команд:


USE [master];
ALTER DATABASE [Имя_Базы] SET RECOVERY SIMPLE;
GO
USE [Имя_Базы];
DBCC SHRINKFILE (Имя_Файла_Лога, 1);
GO
USE [master];
ALTER DATABASE [Имя_Базы] SET RECOVERY FULL;
GO

Проанализируем, что происходит при выполнении этого скрипта. Если на этапе сжатия файла (DBCC SHRINKFILE) или из-за банальной нехватки свободного дискового пространства происходит аварийное завершение сессии, скрипт прерывает свою работу. До третьей инструкции (возврата в режим FULL) дело просто не доходит. В результате база данных остается в режиме SIMPLE, а администратор узнает об этом только тогда, когда очередное регламентное резервное копирование журнала транзакций завершается с ошибкой.

Шаг 1. Поиск виновника через системный журнал SQL Server Error Log

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


EXEC xp_readerrorlog 0, 1, N'Setting database option RECOVERY', NULL, NULL, NULL, N'desc';

В результатах выполнения этого запроса мы увидим строки вида: Setting database option RECOVERY to SIMPLE for database [Имя_Базы]. Зафиксировав точное время этого события, мы сможем сопоставить его с логами планировщика задач Windows, технологическим журналом 1С или планами обслуживания SQL Server.

Шаг 2. Расследование с помощью стандартной трассировки (Default Trace)

Если системный журнал зафиксировал факт изменения модели восстановления, но не дает ответа на вопрос, кто именно выполнил команду (какое приложение или под какой учетной записью), нам поможет стандартная системная трассировка, которая по умолчанию активна в MS SQL Server. Выполним следующий запрос, чтобы извлечь подробности события изменения параметров объекта (Event Class 164 — Object:Altered):


DECLARE @tracefile NVARCHAR(260);

-- Получаем путь к текущему файлу стандартной трассировки
SELECT @tracefile = path 
FROM sys.traces 
WHERE is_default = 1;

-- Читаем события изменения объектов
SELECT 
    StartTime,
    DatabaseName,
    LoginName,
    HostName,
    ApplicationName,
    TextData
FROM sys.fn_trace_gettable(@tracefile, DEFAULT)
WHERE EventClass = 164 -- Object:Altered
  AND DatabaseName = 'Имя_Вашей_Базы'
ORDER BY StartTime DESC;

Этот скрипт покажет нам точное имя учетной записи (LoginName), имя компьютера (HostName) и программу (ApplicationName), из которой был инициирован запрос на изменение модели восстановления. Это позволит быстро определить, был ли это регламентный план обслуживания, внешняя утилита резервного копирования или ручное вмешательство одного из администраторов.

Шаг 3. Внедрение DDL-триггера для автоматического аудита

Если проблему не удалось локализовать сразу, мы можем настроить постоянный превентивный мониторинг. Для этого создадим DDL-триггер на уровне сервера. Он будет срабатывать на любое изменение структуры баз данных (включая инструкции ALTER DATABASE) и записывать подробную информацию в специальную лог-таблицу или в журнал событий Windows.

Создадим служебную базу данных и таблицу для хранения логов изменений:


CREATE DATABASE [DB_Audit];
GO

USE [DB_Audit];
GO

CREATE TABLE [dbo].[RecoveryModelChangesAudit] (
    [ID] INT IDENTITY(1,1) PRIMARY KEY,
    [EventTime] DATETIME DEFAULT GETDATE(),
    [DatabaseName] SYSNAME,
    [LoginName] VARCHAR(255),
    [HostName] VARCHAR(255),
    [AppName] VARCHAR(255),
    [CommandText] NVARCHAR(MAX)
);
GO

Теперь создадим сам DDL-триггер на уровне всего SQL-сервера:


CREATE TRIGGER [Audit_Recovery_Changes_Trigger]
ON ALL SERVER
FOR ALTER_DATABASE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @DatabaseName SYSNAME = @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');
    DECLARE @LoginName VARCHAR(255) = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(255)');
    DECLARE @HostName VARCHAR(255) = @EventData.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(255)');
    DECLARE @AppName VARCHAR(255) = APP_NAME();
    DECLARE @CommandText NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');

    -- Фильтруем только те команды, которые переводят базу в SIMPLE
    IF @CommandText LIKE '%RECOVERY%SIMPLE%'
    BEGIN
        INSERT INTO [DB_Audit].[dbo].[RecoveryModelChangesAudit] 
            ([DatabaseName], [LoginName], [HostName], [AppName], [CommandText])
        VALUES 
            (@DatabaseName, @LoginName, @HostName, @AppName, @CommandText);
    END
END;
GO

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

Шаг 4. Устранение причин сбоя и оптимизация обслуживания

Выяснив, какая регламентная процедура приводит к сбою, необходимо полностью пересмотреть логику обслуживания баз данных 1С на сервере. Рассмотрим основные рекомендации:

Откажитесь от регулярного сжатия файлов базы данных (Shrink): Постоянное сжатие файлов баз данных и логов транзакций — это крайне вредная операция. Она приводит к жесткой фрагментации индексов, из-за чего падает производительность 1С, увеличивается нагрузка на дисковую подсистему, а файлы базы данных при первой же транзакции начинают снова расти, забирая ресурсы процессора на операцию авторасширения. Размер файлов данных и лога должен быть стабильным и соответствовать пиковым нагрузкам вашей системы.
Настройте частый бэкап журнала транзакций: Чтобы файл .ldf не разрастался до критических размеров, настройте регулярное создание резервных копий транзакционного лога (например, каждые 15–30 минут). Именно операция резервного копирования лога очищает виртуальные файлы (VLF) внутри файла журнала, делая их доступными для повторного использования новыми транзакциями.
Используйте профессиональные решения для обслуживания: Вместо написания собственных ненадежных скриптов или использования стандартных визуальных планов обслуживания (Maintenance Plans) в SQL Server, мы настоятельно рекомендуем использовать проверенное временем бесплатное решение от Ola Hallengren. Скрипты Олы Халленгрена корректно обрабатывают ошибки, не ломают модель восстановления и признаны стандартом де-факто в сообществе администраторов баз данных по всему миру.
Ограничьте права учетных записей: Проверьте права пользователя, под которым сервер приложений 1С:Предприятия подключается к MS SQL Server. Учетная запись 1С не должна иметь роли sysadmin или прав на изменение параметров самой базы данных (ALTER DATABASE). Ей достаточно прав db_owner непосредственно внутри целевой базы данных. Это предотвратит любые несанкционированные изменения конфигурации СУБД со стороны прикладного решения.

Применяя эти методы на практике, мы обеспечим надежную и бесперебойную работу СУБД, исключим внезапные переключения режимов восстановления и гарантируем сохранность критически важных данных систем 1С.