Événements de croissance, réduction des fichiers d’une base

Toutes les opérations de croissance ou de réduction des fichiers d’une base sont des opérations conduisant à des dégradation des performances (opérations couteuses, perturbantes, génération de fragmentation physique irréfragable des fichiers…). Il faut absolument éviter que ce genre d’opération se produise de manière implicite ou explicite (commande DBCC SHRINKFILE par exemple, ou encore tâche de réduction des fichiers du plan de maintenance). Voici une requête pour débusquer ces opérations dans l’historique du suivi de diagnostic interne de SQL Server.

Si vous êtes victime d’une telle problématique veillez :

La requête pour ce faire est la suivante :

-- rechercher les événements de croissance des fichiers de toutes les bases
DECLARE @FILE_PATH          NVARCHAR(1024), 
        @SQL                NVARCHAR(max),
        @CMDSHELL           BIT,
        @SHOW_ADV_OPTIONS   BIT;

SELECT @FILE_PATH = LEFT(path, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) 
FROM   sys.traces WHERE is_default = 1;

SELECT @SHOW_ADV_OPTIONS = CAST(value_in_use AS BIT) 
FROM   sys.configurations 
WHERE  name = 'show advanced options';
IF @SHOW_ADV_OPTIONS = 0
BEGIN
   EXEC ('EXEC sp_configure ''show advanced options'', 1;')
   EXEC ('RECONFIGURE;')
END;

SELECT @CMDSHELL = CAST(value_in_use AS BIT) 
FROM   sys.configurations 
WHERE  name = 'xp_cmdshell';
IF @CMDSHELL = 0
BEGIN
   EXEC ('EXEC sp_configure ''xp_cmdshell'', 1;')
   EXEC ('RECONFIGURE;')
END;

EXEC sp_configure 'xp_cmdshell', 1;

SET @SQL = N'xp_cmdshell ''DIR /B "' + @FILE_PATH + N'*.trc"''';

DECLARE @T TABLE (K INT IDENTITY, L NVARCHAR(1024));

INSERT INTO  @T EXEC (@SQL);

IF @CMDSHELL = 0
BEGIN
   EXEC ('EXEC sp_configure ''xp_cmdshell'', 0;')
   EXEC ('RECONFIGURE;')
END

IF @SHOW_ADV_OPTIONS = 0
BEGIN
   EXEC ('EXEC sp_configure ''show advanced options'', 0;')
   EXEC ('RECONFIGURE;')
END

DELETE FROM @T WHERE L IS NULL;

WITH TRC AS 
(SELECT L FROM @T)
SELECT E.name AS EventName, T.DatabaseName, 
       T.FileName, MF.type_desc AS FileType,
       T.StartTime, T.EndTime, T.Duration / 1000.0 AS Duration_ms,
       CAST(SUM(T.Duration) OVER(PARTITION BY DatabaseID) / 1000.0 
          AS DECIMAL(16,2)) AS DB_Duration_ms,
       DATEDIFF(millisecond, MIN(T.StartTime) OVER(PARTITION BY T.DatabaseName), 
                      MAX(T.EndTime) OVER(PARTITION BY T.DatabaseName)) / 86400000.0
          AS PERIODE_JOUR
FROM   TRC
       CROSS APPLY sys.fn_trace_gettable(@FILE_PATH + L, NULL) AS T
       JOIN sys.trace_events AS E 
          ON T.EventClass = E.trace_event_id
       JOIN sys.master_files AS MF 
          ON T.DatabaseID = MF.database_id AND T.FileName = MF.name
WHERE  E.name LIKE '%File Auto%'
ORDER  BY T.DatabaseName, T.StartTime DESC;

Comme a son habitude WordPress, malgré tous les pluggins de la terre étant incapable de ne pas bousiller le code posté dans le présent article, voici un fichier contenant le code à exécuter pour connaître les opérations de croissance de vos fichiers…
rechercher les événements de croissance et réduction des fichiers de toutes les bases SQL Server, le fichier SQL

par exemple, sur un serveur, le lancement de ce code donne :

Vos commentaires sont les bienvenus !

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *