Chris Harshman - Monday, February 19, 2018 2:00 PM

Mike Scalise - Monday, February 19, 2018 12:47 PM

pietlinden - Sunday, February 18, 2018 2:36 PM

Then you'd have to correlate that with what's going on on your server. What's running when - using Profiler or something like it.(Can this be done with Extended Events?)

Then you'd just correlate the growth spikes with stored procedures etc are running just before the spike happens.

Thanks for the suggestion! I'm actually thinking of doing something like this with sp_WhoIsActive...running it every minute or so and logging activity to help get a look into what's going on at a given period of time...

Are the transaction log files growing during these times or are they always big and just filling up more over these hours?  If the transaction log files are growing, that information actually gets captured in the default trace (EventClass 92 = Data File, 93 = Log File), which you can see like this:
DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM  sys.traces
WHERE is_default = 1;

SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, td.EndTime,
  td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
FROM sys.fn_trace_gettable(@path, DEFAULT) td
  INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE td.EventClass IN (92,93)
ORDER BY td.StartTime;