none
Any way to determine last time file growth happened?

    问题

  • I am not looking for something going forward like profiler, etc.  I want to know if there is a way to historically look back and determine when file growth occurred. I have an error I am trying to resolve and think it might be associated with the tempdb file growth.  Is there a way to find out the last time(s) the tempdb grew?

    2008年9月17日 14:41

答案

  • Hi there,

     

    You can review the history of autogrow and autoshrink event by using the standard reports that are available with via Management Studio as of Service Pack 2.

     

    Open Management studio, expand databases folder and then right click the database you wish to review.

    Then choose Reports>Standard Reports > Disk Usage.

     

    You can then expand the appropriate report option by clicking the plus sign.

     

    Let us know how you get on.

     

    Cheers,

    2008年9月17日 15:25

全部回复

  • To my best knowledge, no you can't tell historically, the last time the file was grown.  I ran through the DMV's and couldn't find one that would have that informaton.  You should be monitoring and growing your datafiles manually, leaving Autogrow on as a safety net, and not as a primary growth mechanism.

     

    2008年9月17日 14:55
    版主
  • Hi there,

     

    You can review the history of autogrow and autoshrink event by using the standard reports that are available with via Management Studio as of Service Pack 2.

     

    Open Management studio, expand databases folder and then right click the database you wish to review.

    Then choose Reports>Standard Reports > Disk Usage.

     

    You can then expand the appropriate report option by clicking the plus sign.

     

    Let us know how you get on.

     

    Cheers,

    2008年9月17日 15:25
  • Yes, you can query the default trace to get this information.  The default trace is enabled by default on SQL 2005 and greater. This allows you to get at a whole lot of other information as well including object creation, deletion, and update, auditing etc..

     

    Code Snippet

    --check if default trace is enabled

    select * from sys.configurations where configuration_id = 1568

     

    --get the current trace rollover file

    --use this path with the log.trc file in the path below.

    --this will cause a file rollover to get all the data

    select * from ::fn_trace_getinfo(0)

     

    --list of events object deleted, object altered

    select *

    from sys.trace_events

     

    --list of categories e.g. database, sp etc..

    select *

    from sys.trace_categories

     

    SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name

    FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)

          inner join sys.trace_events e

                on eventclass = trace_event_id

           INNER JOIN sys.trace_categories AS cat

                ON e.category_id = cat.category_id

    where databasename = 'MyDB' and

          cat.category_id = 2 and --database category

          e.trace_event_id in (92,93) --db file growth

     

     

     

     

     

    2008年9月17日 16:51
    版主
  • Hi Dave,

    Default trace can be used for getting information on Log file growths. But the default trace only can go back to certain time.

    When you mean historic, if its within Default trace covering time then you can view this information. 

    The default trace gets full depending on activity on server.

    Quick way to see this information is using

    Reports>Standard Reports > Disk Usage which uses Default trace.

    Hope this helps :)


    Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/

    2012年7月6日 12:19