SQL Script to track database growth date and time

Leave a comment (0) Go to comments

A DBA need to plan for database file size and he should ensure avoiding autogrow of data files and log files, where ever possible as file growth operation on a database server is a extremely expensive operation.

To accomplish this objective, we should also know what is the frequency of database files growth and when it was last grown.

Here is a script to track when you database files have grown

DECLARE @path NVARCHAR(1000) SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) + '\log.trc' FROM sys.traces WHERE id = 1 SELECT databasename, e.name AS eventname, cat.name AS [CategoryName], starttime, e.category_id, loginname, loginsid, spid, hostname, applicationname, servername, textdata, objectname, eventclass, eventsubclass FROM ::fn_trace_gettable(@path, 0) INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id WHERE e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' ) ORDER BY starttime DESC


 

This information is fetched from default trace. so this information is temporarily available for the period which default trace is available.

EOF - SQL Script to track database growth date and time, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.