Skip to content

How frequently is my database auto shrinking?

Although it is not a good practice to enable auto shrink database option. One main reason is that you cannot predict as when will auto shrink happen. Just incase you have enabled auto shrink and want to know how frequently is auto shrink running, then this script will be helpful to you.

DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH,1,Len(PATH) ñ Charindex('\',Reverse(PATH))) + '\log.trc'
FROM   sys.traces<
WHERE  id = 1
SELECT database name,
te.name AS eventname, start time
FROM   ::fn_trace_gettable(@path, 0)
INNER JOIN sys.trace_events the
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON te.category_id = cat.category_id
WHERE  te.name IN( 'Data File Auto Shrink', 'Log File Auto Shrink' )
ORDER  BY databasename,starttime DESC;