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?

https://dba.stackexchange.com/questions/129526/sql-server-history-of-growth-and-shrink-events — Mitch Wheat, Sep 06 '18 at 09:02
thank you. I did not find that case when searching. good one — Lava, Sep 11 '18 at 14:53

score 0 · Answer 1 · answered Sep 06 '18 at 09:38

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

score 0 · Answer 2 · answered Sep 06 '18 at 09:51

0

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 ;)

answered Sep 06 '18 at 09:51

Juozas

916
10
17

As demonstrated Mitch, running additional extended session is not necessary. As server has default extended events session with information you need (please follow Mitch's link) – Juozas Sep 06 '18 at 10:13

score 0 · Answer 3 · answered Sep 06 '18 at 21:49

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

thank you for good reply. my DB have no autoshrink set on and no sql agent is running jobs. but there is a Tivoli agent doing things I don't see. I am talking to those controlling the Tivoli now ... — Lava, Sep 11 '18 at 14:55

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

3 Answers3

Linked