Voici un script SQL pour réduire tous les journaux de transactions (appelés à tort journaux de log…) des bases de données de production d’une instance MS SQL Server…
Cette fonction est nécessaire pour nettoyer les noms des objets qui ne sont pas conforme à la norme SQL.
CREATE FUNCTION dbo.F_CLEAN_SQLID(@SQL_ID NVARCHAR(128))
RETURNS NVARCHAR(128)
AS
BEGIN
IF @SQL_ID IS NULL RETURN NULL;
IF @SQL_ID ='' RETURN '';
DECLARE @I INT, @C NCHAR(1), @RETVAL NVARCHAR(128);
SELECT @I = 1, @RETVAL = '';
WHILE @I <= LEN(@SQL_ID)
BEGIN
SET @C = SUBSTRING (@SQL_ID, @I, 1);
IF @C COLLATE French_CS_AS BETWEEN 'A' AND 'Z'
OR @C COLLATE French_CS_AS BETWEEN 'a' AND 'z'
OOR (@C BETWEEN '0' AND '9' AND @I > 1)
OR @C = '_'
SET @RETVAL = @RETVAL + @C;
ELSE SET @RETVAL = @RETVAL + '_';
SET @I = @I + 1;
END
RETURN @RETVAL;
END
GO
Voici maintenant le script qui fait tout le boulot. Il procède à une sauvegarde des journaux de transaction vers un répertoire de destination, ici C:\temp puis réduit la taille des journaux avec un SHRINKFILE.
DECLARE @SQL nvarchar(max);
SET @SQL = '';
WITH
T0 AS
(SELECT d.name AS databaseName, mf.name AS filName
FROM sys.master_files AS mf
INNER JOIN sys.databases AS d
ON d.database_id = mf.database_id
WHERE d.name NOT IN ('master','model','msdb','tempdb') -- pas une base système
AND d.state_desc = 'ONLINE' -- la base de données en ligne
AND d.source_database_id IS NULL -- la base de données n'est pas une capture instantanée
AND d.recovery_model_desc <> 'SIMPLE' -- elle est en mode de récup non simple
AND d.is_read_only = 0 -- elle n'est pas en lecture seule
AND mf."type" = 1 -- on s'intérese aux dichiers de JT
),
T1 AS
(SELECT DISTINCT 1 AS COMMAND_TYPE,
'BACKUP LOG [' + databaseName +'] TO DISK = ''C:\Temp\'
+ dbo.F_CLEAN_SQLID(databaseName)
+ '_log.bak'';' AS COMMANDE_SQL
FROM T0
UNION ALL
SELECT 2, 'use [' + databaseName + '];DBCC SHRINKFILE (''' + filName + ''', 1) WITH NO_INFOMSGS;'
FROM T0)
SELECT @SQL = @SQL + COMMANDE_SQL
FROM T1
ORDER BY COMMAND_TYPE;
EXEC (@SQL);
--------
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 * * * * *
Que d’erreur !!!! Merci en tout cas, j’ai rectifié le script.
Quelques petites erreurs dans le script détectées:
AND d.recovery_model ‘SIMLE’
il faut utiliser la colonne d.recovery_model_desc et c’est SIMPLE et non simle ce qui donne
AND d.recovery_model_desc ‘SIMPLE’
SELECT 2, ‘DBCC SHRINKFILE ( »’ + mf.name + »’, 1) WITH NO_INFOMSGS;’
ce n’est pas mf.name mais filename qu’il faut mettre ( l’alias qui a été mis dans la CTE, d’ailleurs j’utiliserais un autre nom de mon coté car filename est un mot cle, ceci dit ca fonctionne quand meme avec filename)
Sur la ligne + ‘_log.bak »’ AS COMMANDE_SQL j’ajouterais un ; afin de bien separer les commandes ce qui donne :
+ ‘_log.bak »;’ AS COMMANDE_SQL
Enfin pour que le dbcc fonctionne de mon coté il faut etre sur la base en question, j’ai donc ajouté un use datebasename devant le dbcc shrinkfile ce qui donne :
SELECT 2, ‘use [‘ + databasename + ‘];DBCC SHRINKFILE ( »’ + filenames + »’, 1) WITH NO_INFOMSGS;’
Eh la ca fonctionne nickel pour moi.
Merci SqlPro