Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
o man, so your entire content database is locking up every time you need another 1MB storage? –  Nat Jun 30 '09 at 2:23
    
Yeah, that's a drag to be sure. –  squillman Jul 1 '09 at 16:16
add comment

4 Answers

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.

share|improve this answer
    
"Autogrowths are reported in the SQL logs or the Application event log." I don't see this happening in either place. Do you know how to enable this logging? –  Sam Jul 1 '09 at 15:54
    
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. –  squillman Jul 1 '09 at 16:10
    
I created a database with 2mb of space and then imported into it using ssis. The size was increased to 84mb at 1mb autogrowths. Thanks for the code, but it didn't return any rows. –  Sam Jul 1 '09 at 17:22
    
And on our prod server the only entry I see is this (I would expect more): Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 2453 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size. –  Sam Jul 1 '09 at 17:33
    
...sorry for wasting your time on the logging of data file autogrowths there. What I was looking at on my server log was just someone's typo in the filename and was an entry for a db log file. I guess for data files it's up to trace events or clever use of system stored procedures to monitor file sizes over time. –  squillman Jul 1 '09 at 18:58
add comment

As further ammunition for you, checkout this blog post I did which discusses data file auto-growth: Importance of data file size management.

share|improve this answer
add comment

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;
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.