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

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

Сначала объявим переменные, необходимые для нашего скрипта:

DECLARE @Name      varchar(50) -- имя базы данных
      , @Sqlstr    nvarchar(max) = '' -- первая SQL-строка
      , @Sqlstrx   nvarchar(max) = '' -- вторая SQL-строка
      , @Log_Name  sysname -- имя журнала (не имя файла, они могут отличаться)

Затем мы будем использовать курсор для перебора всех баз данных (за исключением системных баз данных) и уменьшения их журнальных файлов:

DECLARE db_cursor CURSOR
FOR SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' )
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Name

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

WHILE @@Fetch_Status = 0
BEGIN
    -- Здесь необходим оператор USE, чтобы получить правильное имя журнала
    SET @Sqlstrx += 'USE [' + @Name + ']' + CHAR(13)
    -- Получаем имя журнала (не имя файла)
    SET @Sqlstrx += 'SELECT @Log_Name = name FROM sys.database_files WHERE type_desc = ''Log''' + CHAR(13)
    -- Выполняем @Sqlstrx и объявляем @Logname как выходную переменную
    EXEC sp_executesql
         @Sqlstrx
       , N'@Log_Name sysname output'
       , @Log_Name = @Log_Name OUTPUT;

    -- Уменьшаем журнальный файл
    SET @Sqlstr += 'USE [' + @Name + ']' + CHAR(13)
    SET @Sqlstr += 'ALTER DATABASE [' + @Name + '] SET RECOVERY SIMPLE;' + CHAR(13)
    SET @Sqlstr += 'DBCC SHRINKFILE ([' + @Log_Name + '], 1);' + CHAR(13)
    SET @Sqlstr += 'ALTER DATABASE [' + @Name + '] SET RECOVERY FULL;' + CHAR(13)
    EXECUTE sp_executesql @Sqlstr

    FETCH NEXT FROM db_cursor INTO @Name
END

CLOSE db_cursor
DEALLOCATE db_cursor

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

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

Мы надеемся, что эта статья предоставила вам полезный метод управления ростом журнальных файлов в SQL Server. Не стесняйтесь делиться своими мыслями и предложениями в разделе комментариев ниже.