-- 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;