Our SharePoint databases are set to the (nonsensical) 1MB default autogrow out-of-the-box, and I need to "convince" our application owner that this is wrong. Is there a means to monitor the autogrow activity of SQL Server?
I would like to be able to report how frequently this activity is occuring with the 1MB setting.
As further ammunition for you, checkout this blog post I did which discusses data file auto-growth: Importance of data file size management.
Log file autogrowths are reported in the SQL logs or the Application event log. You can also use things like SQL Trace or SQL Profiler to monitor SQL events. Here's a MSDN article that discusses monitoring SQL events.
EDIT: In the Application event log look for Event ID 5144 for autogrowth cancel events and 5145 for successful/completed autogrowth events.
EDIT2: To look for db log file auogrowth events in your SQL log you can use this:
EXEC xp_readerrorlog 0,1,'autogrow'
Increment the 0 in order to have xp_readerrorlog use archived error log files. 0 to (n-1) where n is the number of error log files you have.
You can also set up event notifications for an autogrow event. Something like this:
CREATE EVENT NOTIFICATION data_file_autogrow_notifier
ON DATABASE
FOR DATA_FILE_AUTO_GROW
TO SERVICE 'NotifyAutogrow', 'current database' ;
or for the log file:
CREATE EVENT NOTIFICATION log_file_autogrow_notifier
ON DATABASE
FOR LOG_FILE_AUTO_GROW
TO SERVICE 'NotifyAutogrow', 'current database' ;
Where NotifyAutogrow is the name of a Service Broker instance. More info here. You would need to set this service up for your environment.
It's just always enabled, you can't disable SQL logging. If you're not seeing autogrowth events then chances are you haven't had any happen in the timeframe that your log covers. See the edit to my answer for a sql you can use to look for the autogrowth in your SQL log.
It depends on what you're talking about.
In my experience, SQL Server did not log autogrow to ERRORLOG as has been suggested, instead it's logged in the default trace, and the default trace can be disabled and enabled.
To check if it's enabled, see:
select name, value_in_use
from sys.configurations
where name='default trace enabled'
In case it's disabled, you can enable it:
sp_configure 'default trace enabled', 1
go
Don't forget to run RECONFIGURE afterwards.
To check for autogrow events you can use:
SELECT databaseid, filename, SUM(IntegerData*8) AS Growth, Duration, StartTime
FROM ::fn_trace_gettable('C:\SQL Server\MSSQL10_50.INSTANCENAME\MSSQL\Log\log_4.trc', default)
WHERE EventClass = 92 OR EventClass = 93
GROUP BY databaseid, filename, IntegerData, Duration, StartTime
Where the parameter to fn_trace_gettable is the name of the current (or archived) trace.
You can find the path for the current trace as follows:
SELECT path FROM sys.traces WHERE is_default = 1;
Log file autogrowths are reported in the SQL logs or the Application event log. You can also use things like SQL Trace or SQL Profiler to monitor SQL events.
