SQL: Review Database File Growth

We can do this checking the default trace.


--
-- Review Database File Growth
--
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = [path]
FROM [sys].[traces]
WHERE [is_default] = 1
SELECT [StartTime],
DB_NAME([databaseid]) AS [DatabaseName],
[Filename],
SUM(([IntegerData] * 8) / 1024) AS [Growth in MB],
([Duration] / 1000) AS [Duration in seconds],
RIGHT('0' + CAST(([Duration] / 1000) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0'
+ CAST((([Duration] / 1000) / 60) % 60 AS VARCHAR),
2) + ':' + RIGHT('0'
+ CAST(([Duration] / 1000)
% 60 AS VARCHAR), 2) [TimeHHMMSS]
FROM ::
FN_TRACE_GETTABLE(@trcfilename, DEFAULT)
WHERE ([EventClass] = 92
OR [EventClass] = 93
)
--AND StartTime >= DATEADD(WEEK, -1,GETDATE())
GROUP BY [StartTime],
[Databaseid],
[Filename],
[IntegerData],
[Duration]
ORDER BY [StartTime]

Comments are closed.

Post Navigation