SQL Server Log history of shrink events

Question

SQL Server Log history of shrink events

Admingod

SSCertifiable

Points: 5997
More actions
December 12, 2018 at 9:19 am

#382444

Is there a way to look at the complete history of shrink events for the last month on SQL Server 2014? I noticed the log file is shrunk on certain day, trying to see who has issued the shrunk command.Please advise?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

not really, the best you can get is what is in the default trace using query below or from disk usage report

SELECT TE.name AS [EventName] ,
   v.subclass_name ,
   T.DatabaseName ,
   t.DatabaseID ,
   t.NTDomainName ,
   t.ApplicationName ,
   t.LoginName ,
   t.SPID ,
   t.StartTime ,
   t.RoleName ,
   t.TargetUserName ,
   t.TargetLoginName ,
   t.SessionLoginName
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                     f.[value]
                  FROM sys.fn_trace_getinfo(NULL) f
                  WHERE f.property = 2
                  )), DEFAULT) T
   JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
   JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
               AND v.subclass_value = t.EventSubClass

Admingod SSCertifiable Points: 5997 More actions · Answer 2

Thanks! I tried both option, nothing i could see for the last month. Option 1 query did not show anything for last month. Option 2,Disk usage report has no entry found for autogrow/autoshrink. Do we loose the events if ever the server is rebooted?

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Admingod - Wednesday, December 12, 2018 9:51 AM
Thanks! I tried both option, nothing i could see for the last month. Option 1 query did not show anything for last month. Option 2,Disk usage report has no entry found for autogrow/autoshrink. Do we loose the events if ever the server is rebooted?

The error log is recycled. You may need to look back into the previous logs.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

goher2000 SSCertifiable Points: 5479 More actions · Answer 4

goher2000

SSCertifiable

Points: 5479

December 12, 2018 at 11:13 am

#2016135

deleted

Admingod SSCertifiable Points: 5997 More actions · Answer 5

Thanks! Is there a way to configure notification to catch if ever someone issues and shrink log file or data file command?

Michael L John One Orange Chip Points: 27055 More actions · Answer 6

Admingod - Wednesday, December 12, 2018 11:38 AM
Thanks! Is there a way to configure notification to catch if ever someone issues and shrink log file or data file command?

One suggestion is to use xp_readerrorlog. There are search parameters, etc. that you can leverage.

Another suggestion is to figure out why people have the rights to do this, why they feel the need to do this, and either educated them or remove their rights.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

goher2000 SSCertifiable Points: 5479 More actions · Answer 7

another one.

DECLARE @path VARCHAR(8000)
declare @trcfile table (path varchar(8000))
declare @loc varchar(8000)

SELECT @loc= 'dir /B "' + replace(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(255)),'ERRORLOG','') + '*.trc"'
insert into @trcfile
exec xp_cmdshell @loc

delete from @trcfile where path is null;

declare c1 cursor for
select replace(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(255)),'ERRORLOG','')+path from @trcfile
open c1
fetch c1 into @path
while @@FETCH_STATUS=0
begin
SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (116) AND TextData like 'DBCC%SHRINK%'
ORDER BY StartTime DESC
fetch next from c1 into @path
end
close c1
deallocate c1

Admingod SSCertifiable Points: 5997 More actions · Answer 8

Error log does not have any information for the DBCC shrink. Also, i looked at the trace files in the error log directory and those trace files does not have information for last month.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 9

Error log doesn't record shrink events. Your option is to pull that information from default trace and Yes, default trace doesn't retain information for ever. If your server got rebooted(or SQL Services got recycled), you might not find that information from last month in your current default trace which is running.