SQL Script: Database Auto Grow and/or Auto Shrink Report

Below is a SQL script that can be used to list recent auto grow and/or auto shrink events that happened on your SQL Server. It queries the SQL Server default trace. By default, default trace is enabled (you can check this by running: sp_configure 'default trace enabled').

DECLARE @TracePath NVARCHAR(1000);

-- Get the file path for the default trace
SELECT    
     @TracePath = 
     REVERSE(SUBSTRING(REVERSE([path]), 
     CHARINDEX('\', REVERSE([path])), 1000)) + 'log.trc'
FROM     
     sys.traces
WHERE     
     is_default = 1;

-- Query to get auto growth and shrink event from the default trace
SELECT
     EventName = te.[name],
     tr.ServerName,
     tr.DatabaseName,
     tr.[FileName],
     FilePath = sf.[filename],
     Duration_in_ms = tr.Duration/1000,
     FileGrowth_in_mb = (tr.IntegerData*8)/1024,
     tr.StartTime,
     tr.EndTime
FROM     
     sys.fn_trace_gettable(@TracePath, DEFAULT) tr
     INNER JOIN sys.trace_events te
          ON tr.EventClass = te.trace_event_id
     INNER JOIN sys.sysaltfiles sf
          ON tr.[FileName] = sf.[name]
WHERE     
     te.name in (
          'Data File Auto Grow',
          'Log File Auto Grow',
          'Data File Auto Shrink',
          'Log File Auto Shrink')
ORDER BY tr.StartTime DESC;

By the way, you can also get the information on recent auto grow and/or auto shrink events in SQL Server Management Studio, by doing the following:

• Open SQL Server Management Studio (SSMS)
• On the Object Explorer (you can press F8 to view Object Explorer), right click on the database that you want to get the auto grow and/or auto shrink events on
• On the context menu, select Reports and then Standard Reports. Then select Disk Usage.
• It would open a Disk Usage report for that particular database. If there is any recent auto grow and/or autoshrink event on that database, you should be able to see the “Data/Log Files Autogrow/Autoshrink Events” section.

Hopefully, you don’t have the auto shrink feature enabled on any of your database. Thus, you don’t have any auto shrink events to worry about.

As for auto grow events, if you notice a lot of them, you might want to consider pre-grow (increase the size of) the data/log file during period of low activity. Another thing that you might consider if you are noticing a lot of auto grow events in your database is that you can make the auto growth size to be bigger.

If you experience frequent auto grow events in your database log file, it can lead to high Virtual Log Files (VLF) count.