I can not able to find any Database Maintenance Plan or individual SQL agent job i.e. explicitly shrinking it so I'm not sure how to find out when it has happened. How I could able to find out when the recent shrink operation on transaction log was done?
A shrink event should be registered within the SQL Server error log. Also, if you are running 2008 or better, you can look at the [system_health default extended events] output. Either of those locations should let you know where the shrink came from. :
You should be able to identify this from the "last modified" time on the "ldf" file (unless it has auto grown in the interim - which seems unlikely as it is still only 1MB) Also ensure..Is the auto shrink database option turned on? You wouldn't see a SQL agent job if that was the case a background task performs these shrink operations Remember some time shrinking creates problem so most of DBAs advise to avoid data file shrinking Take a look [Why you should not shrink your data files/] :
You can also ask your default trace. Something like: IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE name = 'default trace enabled' ) = 1 BEGIN DECLARE @curr_tracefilename VARCHAR(500); DECLARE @base_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @curr_tracefilename = [path] FROM sys.traces WHERE is_default = 1; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SELECT @indx = PATINDEX('%\%',@curr_tracefilename); SET @curr_tracefilename = REVERSE(@curr_tracefilename); SET @base_tracefilename = LEFT(@curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; SELECT ServerName ,DatabaseName ,CASE CONVERT(INT,EventClass) WHEN 92 THEN CAST(EventClass AS VARCHAR(10)) + ' - Data File Auto Growth' WHEN 93 THEN CAST(EventClass AS VARCHAR(10)) + ' - Log File Auto Growth' WHEN 94 THEN CAST(EventClass AS VARCHAR(10)) + ' - Data File Auto Shrink' WHEN 95 THEN CAST(EventClass AS VARCHAR(10)) + ' - Log File Auto Shrinks' WHEN 116 THEN CAST(EventClass AS VARCHAR(10)) + ' - ' + CAST(TextData AS VARCHAR(8000)) ELSE CAST(EventClass AS VARCHAR(10)) + ' - ?' END AS EventClass ,[Filename] ,StartTime ,(Duration / 1000) AS DurationInMS ,CAST((IntegerData * 8.0 / 1024) AS DECIMAL(15,2)) AS ChangeSizeInMB ,SessionLoginName ,ApplicationName FROM ::fn_trace_gettable(@base_tracefilename,DEFAULT) WHERE ((EventClass BETWEEN 92 AND 95) OR (TextData LIKE '%DBCC SHRINK%' AND EventClass=116)) AND SERVERNAME = @@SERVERNAME ORDER BY DatabaseName ASC ,StartTime DESC; END