The SQL Swiss Army Knife #5 - Checking Autogrow times

Hello all,
Here is another one focusing on SQL scripts that may help DBAs, following the series "SQL Swiss Army Knife". This time we are exploring an alternative way of verifying autogrow times besides checking the ErrorLog for any recorded information, and that is when an error 5144 or 5145 occurs.

When these happen, SQL Server will report messages to the Application Event Log and ERRORLOG on a failed autogrow of a database and/or transaction log file that has timed out or has taken a long period of time to complete.

When you see this message, most likely your performance as already taken a hit. This is because operations that require the autogrowth of a file will hold on to resources such as locks or latches for the duration of the grow operation, so everything that needs to act on that file sits there.
Not a nice thing. Also, long waits on latches for allocation pages are likely to be observed and the operation that requires the long autogrow will show a wait type of  PREEMPTIVE_OS_WRITEFILEGATHER. As for data files, this will not be an issue if using the “Instant File Initialization” feature, which is a best practice, but will certainly have an impact on log files, as these must be zeroed out.

So if the recommended best practice of preemptively growing files off-hours just isn’t something you are doing in your organization (please consider it), it can be a good idea to at least check how much time these operations are taking from time to time. We can rely on the default trace for this information, and the following script will extract that information for you. The caveat here is that it this information is limited to the number of events the default trace can hold for a given amount of time.

The output will resemble this:

Hope you find it useful.

Download code here: view_AutoGrow_Times.sql

Comments

• Anonymous
  January 15, 2011
  The comment has been removed
• Anonymous
  December 05, 2012
  Great script!A minor tweak:  EventClass = 92 OR EventClass = 93

  AND DATEDIFF(hh,StartTime,GETDATE())<24 -- Last 24h

  Should be  (EventClass = 92 OR EventClass = 93)

  AND (DATEDIFF(hh,StartTime,GETDATE())<24) -- Last 24h

• Anonymous
  December 05, 2012
  Thank you for the feedback.I will actually do without the OR, changing it to:WHERE EventClass >=  92 AND EventClass <=  95AND DATEDIFF(hh,StartTime,GETDATE())<24 -- Last 24h
• Anonymous
  February 27, 2013
  The comment has been removed
• Anonymous
  February 27, 2013
  Hello Henrik Staun Poulsen,Nothing whatsoever at the moment. I just uploaded again with that CASE commented out.Thanks for spotting this!CheersPL