7

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 ?

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

Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014

Yes there is a way, or should i say various ways to save the data collected from default trace as explained here Collecting the Information in the Default Trace

  1. We could query the default trace files and save aggregated values
  2. We could get the definition of the default trace and create a collection set, which can be used with Management Data Warehouse, which is built in with SQL Server.
  3. We could get the definition of the default trace and create a remote trace from another server and save the data to a database table
  4. We could use a robocopy script to copy the trace files from our server to a different folder or a remote machine, which will leave the files on disk for longer time, giving us the possibility to analyze them

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 ?To capture the manual shrink events i use query like below:

SELECT 
    TextData,
    HostName,
    ApplicationName,
    LoginName, 
    StartTime  
FROM 
[fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_75.trc', DEFAULT) 
WHERE TextData LIKE '%SHRINKFILE%'; ----- Location of default trace will be different ,so kindly check that accordingly

Probable reason you're query does not display manual grow or shrink events because you use events like (93, 95) which are for auto grow events

  • Question is if these "manual" events are even logged there. Based on this, they are not ? select distinct name from sys.fn_trace_geteventinfo(1) ei inner join sys.trace_events e on e.trace_event_id = ei.eventid – Razvan Zoitanu Feb 17 '16 at 12:43
  • Got it: where tr.EventClass in (93, 95) or tr.TextData like 'DBCC SHRINK%' – Razvan Zoitanu Feb 17 '16 at 12:59
  • @TheConfusedDBA, yes that's correct, the query in the answer filters using text data for any entries using manual or involving manual intervention of shrinking log files. – KASQLDBA Feb 17 '16 at 13:21
  • For future reference: SELECT TOP 100 , TextData, HostName, ApplicationName, LoginName, StartTime FROM [fn_trace_gettable]('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_894.trc', DEFAULT) / Location of default trace will be different ,so kindly check that accordingly */ where EventClass in (93, 95) or TextData like 'DBCC SHRINK%'; – Razvan Zoitanu Jan 17 '19 at 15:38
10

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

enter image description here

  • Warning - pulling this report crashed my SQL Management Studio and i lost some work - backup your open queries before running fancy reports! – Ali Feb 9 '18 at 11:20
  • 4
    That must be some bug with SSMS or some nasty thing was going on your system. What SSMS version you are using, hope it is patched to latest SP. I did not hear from anybody that getting this report crashed SSMS – Shanky Feb 9 '18 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.