patternsqlMinor
When and who shrunk the ldf file?
Viewed 0 times
shrunkthefilewholdfwhenand
Problem
I’m trying to find information about when and who shrunk a ldf file but I can't find the information anywhere.
Can someone point me in the right direction?
Can someone point me in the right direction?
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4266.0 (X64)
Sep 26 2012 17:08:07
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)Solution
Below is a sample query to return information from the default trace using T-SQL.
By filtering on
SELECT
trace.DatabaseName
,trace.ObjectName
,te.name AS EventName
,tsv.subclass_name
,trace.EventClass
,trace.EventSubClass
,trace.StartTime
,trace.EndTime
,trace.NTDomainName
,trace.NTUserName
,trace.HostName
,trace.ApplicationName
,trace.Spid
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
LEFT JOIN sys.trace_events AS te ON
trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
tsv.trace_event_id = EventClass
AND tsv.subclass_value = trace.EventSubClass
ORDER BY trace.StartTime;By filtering on
WHERE TEXTDATA LIKE 'DBCC SHRINK%' you will find all Shrink events.Code Snippets
SELECT
trace.DatabaseName
,trace.ObjectName
,te.name AS EventName
,tsv.subclass_name
,trace.EventClass
,trace.EventSubClass
,trace.StartTime
,trace.EndTime
,trace.NTDomainName
,trace.NTUserName
,trace.HostName
,trace.ApplicationName
,trace.Spid
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
LEFT JOIN sys.trace_events AS te ON
trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
tsv.trace_event_id = EventClass
AND tsv.subclass_value = trace.EventSubClass
ORDER BY trace.StartTime;Context
StackExchange Database Administrators Q#183735, answer score: 5
Revisions (0)
No revisions yet.