HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

When and who shrunk the ldf file?

Submitted by: @import:stackexchange-dba··
0
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?

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.

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.