MS SQL. How can I find time-point when log file was shrink?

Question

Someone is shrinking the log files of databases in production. Now and then. It's not good to shrink often.

But how do I find information of when and what sql user did the log file shrinking?

Everyting on MS SQL server is logged so somewhere is the information I seek. Some kind of system view or table?

Answer 1

If you right click on your database, go to reports and the select "Disk Usage" (from memory, I'm not sat at a PC to check) it will show the events.

However, the information is from the default trace so will only show recent events. I believe the default trace is only 5 20MB files.

If it is more infrequent than this, you will have to set up a trace or use profiler (trace is lighter on resources than profiler) and look for Log File Auto shrink events

Answer 2

Go to Server > Management > Extended Events. Then create new session and use Event library :

Then you can view live data:

Or analyze past data:

Hope this helps. If youre interested in reading extended logs file directly using t-sql, I can help too ;)

Answer 3

when and what SQL user did the log file shrinking???

You should be able to determine this from the "last modified" time on the "ldf" file (unless it has autogrown in the interim - which seems unlikely as it is still only 1MB)

Does the database have logged activity happening? Is the autoshrink database option turned on?

You wouldn't see a SQL agent job if that was the case a background task performs these shrink operations. This setting is extremely unrecommended

Log file was shrunk. Who did it?

The default trace can be looked at to determine by whom and when the shrink file was executed.

To search the default trace, first determine if the default trace is enabled. This can be done by running sp_configure as follows:

SQL:

EXEC master.dbo.sp_configure 'show advanced options', 1; 
GO 
EXEC master.dbo.sp_configure 'default trace enabled';
GO

Once the default trace is validated and running, determine the location the trace files are being written to by running the function, fn_trace_getinfo

SQL:

SELECT * 
FROM fn_trace_getinfo(default);
GO

To read the trace file, use fn_trace_gettable. This will pull all the data from the trace file and show it in a table format in SSMS for ease of reviewing

SQL:

SELECT 
    TextData,
    HostName,
    ApplicationName,
    LoginName, 
    StartTime  
FROM 
[fn_trace_gettable]('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_75.trc', DEFAULT) 
WHERE TextData LIKE '%SHRINKFILE%'; ----- Location of default trace will be different ,so kindly check that accordingly