Gestion des journaux de transactions

20 réponses

Si l’on y prend pas garde, notamment à cause d’une mauvaise administration du serveur, un journal de transaction (JT) peut saturer les disques du serveur. Que faire en cas de problème ? Voici différents scénarios et différents scripts pour parvenir à vos fins.

Les journaux de transactions d’une base de données journalisant en mode FULL ou BULK LOGGED croissent indéfiniment. Seule la sauvegarde du JT permet de réduire la journal. Mais un passage en transitoire mode SIMPLE sans utilisateur connecté permet d’obtenir le même effet, sans pour autant générer un fichier de sauvegarde. Voici quelques script SQL pour ce faire…

NOTA : ces scripts utilisent la fonction RESCRIT dont le code figure à l’entrée suivante « Qualité des données : une fonction de correction des littéraux« .

1 – script de sauvegarde complète de bases de données de production sauf journalisation SIMPLE

Modifiez le contenu de la variable @PATH pour indiquer votre chemin de sauvegarde



DECLARE @PATH NVARCHAR(255); -- chemin de sauvegarde 

SET @PATH = 'C:\MesSauvegardes\'; 

DECLARE @SQL NVARCHAR(max); 

SET @SQL = ''; 

SELECT @SQL = @SQL + 'BACKUP DATABASE [' + DB_NAME(DB.database_id)  

                   +'] TO DISK = ''' + @PATH  

                   + dbo.F_RESCRIT(DB_NAME(DB.database_id), 

                                   '- _.àáâãäçèéêëìíîïñòóôõöùúûüýÿabcdefghijklmnopqrstuvwxyzÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',  

                                   '____aaaaaceeeeiiiinooooouuuuyyabcdefghijklmnopqrstuvwxyzAAAAACEEEEIIIINOOOOOUUUUYABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',  

                                   ''  )  

                   + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), CURRENT_TIMESTAMP, 121), '-', ''), ' ', '_'), ':', '') + '.BAK'';' 

FROM   sys.databases AS DB 

WHERE  DB_NAME(DB.database_id) NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution') 

  AND  recovery_model_desc <> 'SIMPLE'; 

EXEC (@SQL);

2 – script de sauvegarde transactionnelle des bases de données de production sauf journalisation SIMPLE

Modifiez le contenu de la variable @PATH pour indiquer votre chemin de sauvegarde



DECLARE @PATH NVARCHAR(255); -- chemin de sauvegarde 

SET @PATH = 'C:\MesSauvegardes\'; 

DECLARE @SQL NVARCHAR(max); 

SET @SQL = ''; 

SELECT @SQL = @SQL + 'BACKUP LOG [' + DB_NAME(DB.database_id)  

                   +'] TO DISK = ''' + @PATH  

                   + dbo.F_RESCRIT(DB_NAME(DB.database_id), 

                                   '- _.àáâãäçèéêëìíîïñòóôõöùúûüýÿabcdefghijklmnopqrstuvwxyzÀÁÂÃÄÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',  

                                   '____aaaaaceeeeiiiinooooouuuuyyabcdefghijklmnopqrstuvwxyzAAAAACEEEEIIIINOOOOOUUUUYABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',  

                                   ''  )  

                   + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), CURRENT_TIMESTAMP, 121), '-', ''), ' ', '_'), ':', '') + '.TRN'';' 

FROM   sys.databases AS DB 

WHERE  DB_NAME(DB.database_id) NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution') 

  AND  recovery_model_desc <> 'SIMPLE'; 

EXEC (@SQL);

3 – script de passage des bases de données de production en mode de journalisation SIMPLE sauf pour celle utilisant déjà ce mode SIMPLE, de manière transitoire afin de préparer la purge du journal de transaction



DECLARE @SQL NVARCHAR(max); 

SET @SQL = 'USE master;CHECKPOINT;'; 

SELECT @SQL = @SQL + 'USE [' + DB_NAME(DB.database_id) + '];' 

                   + 'ALTER DATABASE [' + DB_NAME(DB.database_id) +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'  

                   + 'USE master;' 

                   + 'ALTER DATABASE [' + DB_NAME(DB.database_id) +'] SET RECOVERY SIMPLE;' 

                   + 'ALTER DATABASE [' + DB_NAME(DB.database_id) +'] SET MULTI_USER;' 

                   + 'ALTER DATABASE [' + DB_NAME(DB.database_id) +'] SET RECOVERY FULL;' 

FROM   sys.databases AS DB 

WHERE  DB_NAME(DB.database_id) NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution') 

  AND  recovery_model_desc <> 'SIMPLE'; 

EXEC (@SQL);

4 – script pour réduire physiquement tous les journaux de transaction des bases de données de production sauf journalisation SIMPLE



DECLARE @SQL NVARCHAR(max); 

SET @SQL = ''; 

SELECT @SQL = @SQL + 'USE [' + DB_NAME(DB.database_id)  

                   +'];DBCC SHRINKFILE (' + CAST(file_id AS VARCHAR(32)) +', 1);' 

FROM   sys.master_files AS MF 

       INNER JOIN sys.databases AS DB 

             ON MF.database_id = DB.database_id 

WHERE  type_desc = 'LOG' 

  AND DB_NAME(DB.database_id) NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution') 

  AND recovery_model_desc <> 'SIMPLE'; 

EXEC (@SQL);

Voici une autre façon de faire qui passe la base en mode SIMPLE et revient à son mode normal :



SELECT 'USE [' + d.name +'];'  

       + 'ALTER DATABASE [' + d.name +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'  

       + 'USE master;'  

       + 'ALTER DATABASE [' + d.name +'] SET RECOVERY SIMPLE;'  

       + 'USE [' + d.name +'];'  

       + 'DBCC SHRINKFILE (''' + mf.name +''', 1);'  

       + 'ALTER DATABASE [' + d.name +'] SET RECOVERY ' + recovery_model_desc COLLATE French_BIN2 +';'  

       + 'ALTER DATABASE [' + d.name +'] SET MULTI_USER;' 

FROM  sys.databases AS d  

      INNER JOIN sys.master_files AS mf  

            ON d.database_id = mf.database_id   

WHERE d.state_desc = 'ONLINE'  

  AND source_database_id IS NULL  

  AND recovery_model_desc <> 'SIMPLE'  

  AND mf.type_desc = 'LOG';

Il suffit de lancer la sortie de cette requête pour réduire drastiquement la taille des journaux de transaction.



-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire