LLI Wiki — My Digital Knowledge Base

Réduire (shrink) un fichier journal de transactions

Étapes pour identifier pourquoi un journal ne peut pas être réutilisé, le sauvegarder, réduire le fichier journal et tempdb, avec les scripts et leurs précautions.

Un fichier journal de transactions peut rester volumineux une fois le travail qui l'a rempli terminé. Avant de le réduire (shrink), identifiez pourquoi son espace ne peut pas être réutilisé, libérez cet espace par une sauvegarde du journal, puis réduisez le fichier. Lisez les remarques avant d'exécuter les scripts de réduction.

1. Vérifier pourquoi le journal ne peut pas être réutilisé

SELECT d.name, d.log_reuse_wait_desc
FROM sys.databases AS d;
log_reuse_wait_desc Signification
NOTHING Aucune raison de blocage : l'espace du journal peut être réutilisé, le fichier peut être réduit
OLDEST_PAGE Problème avec le checkpoint
LOG_BACKUP Une sauvegarde du journal est nécessaire avant que l'espace puisse être réutilisé ; la réduction n'est pas possible tant que ce n'est pas fait

2. Examiner les transactions ouvertes et l'espace du journal

DBCC OPENTRAN;
DBCC LOG ('AppDb', 4);

EXEC sp_spaceused;
DBCC SQLPERF (LOGSPACE);

3. Sauvegarder le journal

Cet exemple utilise la procédure DatabaseBackup de la solution de maintenance SQL Server d'Ola Hallengren.

EXECUTE master.dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\Backup',
    @BackupType = 'LOG',
    @ChangeBackupType = 'Y';

4. Réduire les fichiers journaux de toutes les bases

Le script réduit à 1 Mo le fichier journal de chaque base. Il exécute un checkpoint avant chaque réduction.

DECLARE @Name AS VARCHAR(100)
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @ressult varchar(1000);
declare @size int;

DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ParmDefinition = N'@database varchar(1000), @size int OUTPUT, @ressult varchar(1000) OUTPUT';
    SET @SQLString = 'use [' + @Name + '];
                      CHECKPOINT;
                      select @ressult=name,@size=size from sys.database_files where type_desc=''log''
                      select name,size from sys.database_files where type_desc=''log''
                      CHECKPOINT;
                      '

    print @SQLString
    begin try
        EXEC sp_executesql @SQLString, @ParmDefinition, @database=@Name, @size = @size OUTPUT, @ressult = @ressult OUTPUT
        print @ressult
        SET @SQLString = 'use [' + @Name + '];
                          DBCC SHRINKFILE (['+ @ressult +'], 1)
                          CHECKPOINT;'
        print @SQLString
        EXEC sp_executesql @SQLString, @ParmDefinition, @database=@Name, @size = @size OUTPUT, @ressult = @ressult OUTPUT
        print (@size/1024)
    End try
    begin catch
        print 'test'
    end catch
    --exec sp_executesql 'use'
    FETCH NEXT FROM dbCur
    INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur

5. Suivre l'avancement de la réduction

SELECT
    a.session_id,
    command,
    b.text,
    percent_complete,
    done_in_minutes = a.estimated_completion_time / 1000 / 60,
    min_in_progress = DATEDIFF(MI, a.start_time, DATEADD(ms, a.estimated_completion_time, GETDATE())),
    a.start_time,
    estimated_completion_time = DATEADD(ms, a.estimated_completion_time, GETDATE())
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE command LIKE '%dbcc%';

6. Réduire tempdb

Les étapes suivantes vident les caches, réduisent les fichiers de données de tempdb, puis réduisent la base et chaque fichier à une taille cible en Mo.

USE tempdb
GO
CHECKPOINT
GO
DBCC FREEPROCCACHE -- clean cache
GO
DBCC DROPCLEANBUFFERS -- clean buffers
GO
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
GO
DBCC FREESESSIONCACHE -- clean session cache
GO

DECLARE @name VARCHAR(MAX);
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type = 0;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @name;
    DBCC SHRINKFILE(@name, 0); -- shrink the data file
    FETCH NEXT FROM db_cursor INTO @name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb', 3)
GO

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp2', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp3', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp4', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp5', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp6', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp7', 8192)
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'temp8', 8192)
GO

Remarques

Mastodon LLIMastodon SocialFosstodon