Sunday, January 20, 2019

Who shrunk that database file?

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.

First run this to find your trace file:

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



6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Adding just a bit of automation for the very handy query from SQL Fingers site.

    -- 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%' ;

    ReplyDelete
  4. "Hamster Wheel of Death"!!! You are killing me. But seriously, folks, thanks for the explanations in this article.

    ReplyDelete
    Replies
    1. Yeah, I loved that Hamster reference as well, but cannot take credit... https://www.brentozar.com/blitz/auto-shrink-enabled/

      Delete