Does SQL server respond slow during Auto growth event ?
Auto growth value in my production database.
File Name Initial Size Auto Growth
MDF 115MB By 1 MB, Unlimited
LDF 24MB By 10 percent, Limited to 2,097,152MB
When i verified my production database using the following query every day or every two days this auto growth event is happening. This is a peak hour and lot of transaction are happening this hour. Does SQL server slow down the application during this time ?
DECLARE @path NVARCHAR(260);
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
WHERE is_default = 1;
FileType = CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
FROM sys.fn_trace_gettable(@path, DEFAULT)
EventClass IN (92,93)
See attached image for auto growth event happened on 08-Aug-2014 between 09:40:25.087 to 11:03:45.987.
Should i need to change the auto growth value of the production database ?