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
- Le script de l'étape 4 traite toutes les bases de l'instance. Exécutez-le pour une seule base à la fois, et seulement après l'étape 3 lorsque la raison trouvée à l'étape 1 est
LOG_BACKUP. - Une sauvegarde du journal prise en dehors du planning habituel modifie la chaîne des sauvegardes de journal. Les restaurations ont alors besoin des sauvegardes de journal dans l'ordre, et un outil de sauvegarde tiers qui sauvegarde aussi le journal peut entrer en conflit avec elles.
- La réduction déplace des pages et laisse le fichier fragmenté. Évitez les cycles répétés de réduction et d'agrandissement, et dimensionnez le fichier selon la charge.
DBCC LOGest une commande non documentée, non supportée par Microsoft. Ne l'utilisez que pour le diagnostic.- L'étape tempdb vide les caches de plans et de buffers de toute l'instance, ce qui affecte toutes les sessions. Exécutez-la pendant une fenêtre de maintenance.
- Les noms des fichiers de tempdb et la taille cible de 8192 Mo (8 Go) par fichier sont des exemples. Adaptez-les à votre instance. Les cibles de
DBCC SHRINKFILEsont exprimées en Mo.