0

I'm currently logging the outputs of sp_Blitz, sp_BlitzCache, and sp_BlitzFirst regularly (via SQL jobs).

A particular database's log file grew somewhat exponentially and I believe I can see the grows that occurred from the output from sp_BlitzFirst (via the SizeOnDiskMBgrowth column).

But I was wondering if any of those sp_Blitz... procedures would also be able to tell me the queries that were running that caused the large grows, or at least what queries were running at the time of those grows and then I can do a loose correlation?

  • You can try to track it down using the default trace, or your own XEs, but this would only work if the action that caused it is either directly correlated or happens to also have been captured (a bunch of SELECTs that lead to version store explosion and a tempdb data file increase won't be captured in the default trace). – Aaron Bertrand Mar 18 '19 at 21:34
1

Sure, start with:

SELECT TOP 100 * 
  FROM dbo.BlitzCache 
  WHERE CheckDate BETWEEN '2019-02-18' AND '2019-02-19'
  ORDER BY TotalWrites DESC;

The output of sp_BlitzCache includes the most resource-intensive queries in the plan cache. Starting with that query, you can narrow down what queries wrote the most data during your problem time span.

I like this approach a lot more than the Extended Events one since the XE session would only catch the query that happened to trigger the log growth - which may not be the one that did the most writes. (It might have just been the straw that broke the camel's back.)

Note that these are the queries that did the most writes altogether, though, and those numbers aren't reset each time sp_BlitzCache runs. You may need to do a little more digging to find out which ones ran the most often (or generated the most writes) during a particular time span by joining to the prior runs of that query in dbo.BlitzCache.

| improve this answer | |
  • Thanks Brent! I just had time to jump back on this one and I think I follow all that. Essentially my log of BlitzCache right before the event as a baseline and then check the deltas on TotalWrites (etc) throughout or right after the event to see which queries were most active. – J.D. Apr 9 '19 at 16:34
0

You can find out autogrowth events from default trace (use my script and adjust as per your needs)- timing, size and no of times it occurred.

You can use extended events - my answer to find out what process caused autogrowth.

The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT --> sqlserver.database_file_size_change & sqlserver.databases_log_file_size_changed and ACTION --> sqlserver.sql_text.

Note that since you are on 2008, you are limited on what XEvents will offer. It will be an unsupported version after July 9. Time to migrate off it.

Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file?

No, since they analyze what is there on the server and do not setup any XEvents.

| improve this answer | |

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.