5

I am aware of the disadvantages of AUTO_SHRINK being configured on SQL Server databases. Recently, in a new environment, I discovered several production servers with the Auto Shrink option turned on.

Now, before I change this configuration, I wonder: Since the auto_shrink event starts at 25% unused space I wonder if and/or when this is actually happening on the SQL Servers. I tried to find the relevant SQL-Event-Logfile entries. I started manual shrinking on test databases but there is no Event-Log entry showing up.

Does anyone know how to find out when shrinking has happened within the latest days or weeks on a SQL Server?

3

2 Answers 2

1

Yes you can get the details accordingly from the default trace (read here for more) , depending upon its retention out there from query below:

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;

Also, you can view the same by Standard reports section:

1) Open SQL Server Management Studio (SSMS)

2) On the object explorer highlight the database whose auto shrink event you want to check and make a right click

3) Select Reports and then Standard Reports. Then select Disk Usage

4) It will open a report on the screen and 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.

0
2

Pls see this link :

To summarize (and this worked for me) :

Find out the location of your default trace file using :

SELECT * FROM fn_trace_getinfo(default);

Then use that to query your default trace file :

SELECT top 10 
    TextData,
    HostName,
    ApplicationName,
    LoginName, 
    StartTime  
 FROM 
 [fn_trace_gettable]('C:\MSSQL\Log\log_1110.trc', DEFAULT) 
 WHERE TextData LIKE '%SHRINK%'; 

You can see entries there for operations like DBCC SHRINKDATABASE etc.

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