Much like 'Who dropped that table?', you hope you're the only one shrinking files in your SQL Server database... but if you believe that, then I've got a bridge to sell you. 😏 Again, I really love SQL Server's default trace! Incredibly helpful, with so many different uses!! Here's an example of how to use it to find out who shrank the SQL Server database files.
-- find your default trace file
SELECT * FROM fn_trace_getinfo(default);
Now input your filepath and name from the above query into the FROM in this statement:
-- query it for any shrinkfiles
SELECT
TextData,
HostName,
ApplicationName,
LoginName,
StartTime
FROM
[fn_trace_gettable]('C:\Program Files\Microsoft ...\log_175.trc', DEFAULT)
WHERE
TextData LIKE 'DBCC SHRINKFILE%'
And here you go:
Now you know which files Mr/Ms Lenovo shrank, and when.
Shrinking SQL Server's database files is something you'll do only in worst case scenarios. ie., Your log is blowing up and you are going to run out of space in the middle of the production day. Ok. Shrink it. But you should really try not to make this a repeat activity. For more evidence of why, take a look at the Hamster Wheel of Death explanation from Brent Ozar.
Of course, as a reminder, if your default trace isn't enabled/running, it really should be. Run this to confirm default trace status:
SELECT * FROM sys.configurations WHERE configuration_id = 1568
Adding just a bit of automation for the very handy query from SQL Fingers site.
ReplyDelete-- Determine location for the Default Trace file. Applies to SQL 2005-2019.
-- Declare temp variable to store trace file location.
DECLARE @TraceInfo TABLE
(TraceID INT,
Property INT,
Value VARCHAR(MAX) ) ;
INSERT INTO @TraceInfo select TraceId, Property, CONVERT(VARCHAR(max), Value) AS [Value] FROM :: fn_trace_getinfo (default) ;
DECLARE @AuditLoginTracePath NVARCHAR(256) = (SELECT [VALUE] FROM @TraceInfo WHERE [Value] LIKE '%MSSQL%log[_]%.trc') ;
-- Query Default Trace for any shrinkfile events.
SELECT
TextData,
HostName,
ApplicationName,
LoginName,
StartTime
FROM
[fn_trace_gettable](@AuditLoginTracePath, DEFAULT)
WHERE
TextData LIKE 'DBCC SHRINKFILE%' ;
"Hamster Wheel of Death"!!! You are killing me. But seriously, folks, thanks for the explanations in this article.
ReplyDeleteYeah, I loved that Hamster reference as well, but cannot take credit... https://www.brentozar.com/blitz/auto-shrink-enabled/
Delete