Catch event of auto_shrink in SQL Server Log? [duplicate]

Question

This question already has an answer here:

What event information can I get by default from SQL Server? (1 answer)

Closed 10 years ago.

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?

• Shanky: Are you looking for shrink information in SQl Server errorlog ? Or in traces, where ?
• Magier: @Shanky whereever I can find em! I would like to finde an answer to the question: Did auto shrinks happen in the latest past and influence the performance?
• Shanky: Did you wnet through sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/13/… and stackoverflow.com/questions/5354455/… its just matter of good search

Answer 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.

Answer 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.