Post reply

Question: Is there a way to capture when a Database Auto-grows

  • December 19, 2008 at 5:46 am

    #94855

    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

    #915156

    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

    #915174

    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

    #915204

    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

    #915214

    Thanks, I'll try it

  • December 19, 2008 at 7:08 am

    #915250

    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

    #915791

    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

    #916705

    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