Just for starters, I don't need a primer on how shrinking a database is bad, or why I shouldn't be doing it. Trust me, I understand the implications of shrinking databases, why it shouldn't be done, and I explain this to people online all the time. That being said, this question came to me from a vendor of ours, and I am not sure of the context of the need because I didn't ask, but I'd imagine that he is trying to prove that shrinking is being performed in one of his support cases, and since I like to help people out when they ask for it, so I figured I'd give it a shot.
So where would you look for this kind of information? I wasn't sure where to start to be honest, so I started by shrinking one of my development databases on a development server. Checked the ErrorLog, and found nothing, then through some DMV's and found nothing, then I checked the Database using DBCC PAGE and again found nothing, so I hit up twitter and asked if this could even be found, and got a quick response from Paul Randal letting me know it isn't tracked anywhere in the database, so probably not.
After letting the person who asked me about this know that, and trading a few emails on the subject, it dawned on me that the default trace might be able to tell this information if it hasn't rolled over yet, so I put together a quick test script and Voila, I had found what I was looking for:
DECLARE @filename VARCHAR(MAX)
SELECT @filename = CAST(value AS VARCHAR(MAX))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
AND value IS NOT NULL
SELECT gt.EventClass,
te.Name AS EventName,
gt.TEXTData,
gt.NTUserName,
gt.NTDomainName,
gt.HostName,
gt.ApplicationName,
gt.LoginName,
gt.SPID,
gt.StartTime,
gt.EndTime,
gt.ObjectName,
gt.DatabaseName,
gt.FileName
FROM [fn_trace_gettable](@fileName, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 116
AND TEXTData LIKE '%SHRINK%'
ORDER BY StartTime;
Ok, so now feel free to comment about how bad shrinking databases is, and why I shouldn't do it. I can take it.