March 3, 2009 at 12:25 am
Hi all,
I want to get the latest shrink events' date and details?
Is there a system table or view to display these data?
March 3, 2009 at 3:13 am
If you have SP2 or better installed, you can right click on the database in question, select "reports", then "standard reports", then click on "disk usage".
If you look towards the bottom of the resulting report you'll see "Data/Log Files Autogrow / Autoshrink Events", and if you expand that by clicking on the "+" you'll see a lot of useful information that might be helpful to you.
March 3, 2009 at 4:57 am
Just note those reports are based off the default trace which only keeps a certain amount of data (5 files of 20MB each). Data older is discarded. Also, as far as I know, that's only for autoshrink (which should be disabled) and not for a manual shrink.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2009 at 5:11 am
Yup just for auto-shrink and auto-grow.
Sorry I may have misread the question, this report won't show information on manually initiated shrink or growth operations.
March 3, 2009 at 8:49 am
Something like this, perhaps?
SELECT * FROM fn_trace_getinfo (0); --Get your server's default trace details
SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName,
e.category_id, cat.name, textdata, starttime, endtime, duration, eventclass, eventsubclass, e.name as EventName
FROM ::fn_trace_gettable(' ',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE e.name = 'Audit DBCC Event'
AND textdata like 'DBCC SHRINKFILE%'
IIRC, you only get the starttime populated by default, so you'd need to order by latest starttime to get the last shrink date.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply