December 19, 2008 at 5:46 am
Hi there,
Is there a way to capture/log when the database performs an auto grow command in SQL Server 2000 (and 2005/2008). I would like to be able to track the rate at which these occur so I can size appropriately.
I mean I would love to add several GB to my databases so they don't need to auto grow, but that is not pratical.
Thanks, Brian
December 19, 2008 at 5:52 am
That's actually the way to go.
Log the db size regularly, then project the db size 1-2 years down the road and resize accordingly. Constant auto-grow has nasty effects on performance (not to mention if it happens during a query, it might make it time out).
December 19, 2008 at 6:09 am
Yeah, I know that your method is ideal.
Unfortunately we do full restores of over 100 database from 8 servers to 1 server for reporting and they get upset when I ask for multiple terabytes of disk space to support the system.
We also have issues with our disks right now and I am trying to see if there is a correlation between auto-grows and when we have issues. I just haven't found a counter or alert that logs when the auto grow happens.
Brian
December 19, 2008 at 6:40 am
Start a trace with only the grow (and shrink) events.
You can setup a job to run start a serverside trace to run for 24 hours, and run that job daily.
December 19, 2008 at 6:50 am
Thanks, I'll try it
December 19, 2008 at 7:08 am
I would also check your default trace that is running. Most default traces automatically capture auto grow events.
December 20, 2008 at 5:27 pm
In 2005 if you have default trace running in background run the below mentioned code with the log file location as per your system:
SELECT
trc_evnt.name
,dflt_trc.DatabaseName
,dflt_trc.ApplicationName
,dflt_trc.TextData
,dflt_trc.FileName
,dflt_trc.LoginName
,dflt_trc.StartTime
FROM fn_trace_gettable('F:\SQL2005\MSSQL.1\MSSQL\LOG\log_313.trc', NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
WHERE trc_evnt.name like '%Auto Grow%'
ORDER BY dflt_trc.StartTime DESC
MJ
December 23, 2008 at 5:29 am
Thanks to all.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply