patternsqlModerate
SQL Server history of growth and shrink events
Viewed 0 times
eventshistorysqlservershrinkgrowthand
Problem
Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ? (New instances to support, noticed from the ticketing system that they have previous history of "log drive full", so would like to dig into the root cause before it happens again.)
This was useful: Identify File Growth Events . Came up with below query, but it doesn't show any of the "manual" log shrink events, just the "auto" events. Am I doing this the wrong way, any other place to look for past info ?
This was useful: Identify File Growth Events . Came up with below query, but it doesn't show any of the "manual" log shrink events, just the "auto" events. Am I doing this the wrong way, any other place to look for past info ?
select
te.name as event_name,
tr.DatabaseName,
tr.FileName,
tr.IntegerData,
tr.IntegerData2,
tr.LoginName,
tr.StartTime,
tr.EndTime
--select *
from
sys.fn_trace_gettable(convert(nvarchar(255),(select value from sys.fn_trace_getinfo(0) where property=2)), 0) tr
inner join sys.trace_events te on tr.EventClass = te.trace_event_id
where
tr.EventClass in (93, 95) --can't identify any other EventClass to add here
order by
EndTime desc;Solution
Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ?
There is easy way to get information about Data/log file autogrowth and Auto shrink events from database reports. The report fetches data from default trace. I am not sure how recent would be your report that depends on when was default trace rolled over. The default size is 20 MB after which it will roll over and also note that after SQL Server restart the trace will be rolled over
Connect to SQL Server instance and then right click on database and then select reports and then standard reports and then click disk usage.
In the report which comes look for
In my case it only reported autogrowth event as I don't have auto shrink or any shrink activity running, which I guess you know is utterly bad.
There is easy way to get information about Data/log file autogrowth and Auto shrink events from database reports. The report fetches data from default trace. I am not sure how recent would be your report that depends on when was default trace rolled over. The default size is 20 MB after which it will roll over and also note that after SQL Server restart the trace will be rolled over
Connect to SQL Server instance and then right click on database and then select reports and then standard reports and then click disk usage.
In the report which comes look for
Data/log file autogrowt auto shrink events and expand the + sign this is just below the circular usage report. The report would look like below.In my case it only reported autogrowth event as I don't have auto shrink or any shrink activity running, which I guess you know is utterly bad.
Context
StackExchange Database Administrators Q#129526, answer score: 11
Revisions (0)
No revisions yet.