DBCC shrink file history

Question

I ran shrink file(.mdf) through GUI by selecting Task -> Shrink -> Files and selected second option i.e Reorganize pages before releasing unused space. Now, I would like to know the history of shrink file command which I ran against 50dbs. For e.g the following

1. How much space did it reclaim
2. .mdf file space before running the shrink file command and after running it.

Thanks

Answer 1

SQL Server doesn't keep that type of history by itself. However, you might be able to derive some information by querying the backup history tables in msdb and compare the size of a full backup before and after the shrink.

Answer 2

dflt

The default trace holds some limited information about all DBCC commands, including shrinkage. Using Aaron Bertrand's query as a starting place, we can figure out which database was shrinkaged, and then look at some recent backup history for it.

WITH p as
(
  SELECT 
      [path] = 
          REVERSE
          (
              SUBSTRING
              (
                  p, 
                  CHARINDEX
                  (
                      N'\', 
                      p
                  ), 
                  260
              )
          ) + N'log.trc'
  FROM 
  (
      SELECT 
          REVERSE(t.[path]) 
      FROM sys.traces AS t
      WHERE t.is_default = 1
  ) s (p)
)
SELECT 
   t.TextData,
   ShrunkenHead = 
   SUBSTRING
   (
       t.TextData,
       CHARINDEX
       (
           '''', 
           t.TextData
       ) 
       + 1,
       CHARINDEX
       (
           '''', 
           t.TextData, 
           CHARINDEX
           (
               '''', 
               t.TextData
           ) 
           + LEN('''')
       ) - LEN('''') 
         - CHARINDEX
           (
               '''', 
               t.TextData
           ) 
   ),
   t.NTUserName,
   t.NTDomainName,
   t.HostName,
   t.ApplicationName,
   t.LoginName,
   t.Duration,
   t.StartTime,
   t.EndTime,
   t.Reads,
   t.Writes,
   t.CPU,
   t.Success,
   t.ServerName,
   t.DBUserName,
   t.LoginSid,
   t.SessionLoginName
INTO #p
FROM p 
CROSS APPLY sys.fn_trace_gettable
(
    p.[path], 
    DEFAULT
) AS t
WHERE t.EventClass = 116
AND   t.TextData LIKE 'DBCC SHRINK%';

You need to do a little gymnastics to parse the DBCC command, because the database that gets logged in the trace is the one that the command ran from, not ran against.

Note that I'm including some columns from the trace that I believe will always be NULL for shrinkages, but it could also just be something with what I did to generate test data in the trace that made them less than useful. If they're also NULL for you, well, you're in good company. But I can't make them more useful.

If you need help understanding what the string parsing did in my query did, you can check out my post about it here.

• T-SQL: Get The Text Between Two Delimiters

Once we have that tucked away in a #temp table, we can look at full backup history for the database.

SELECT
    p.*,
    bs.*
FROM #p AS p
OUTER APPLY
(
    SELECT TOP (20)
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.backup_size,
        bs.compressed_backup_size
    FROM msdb.dbo.backupset bs
    WHERE bs.name = p.ShrunkenHead
    AND   bs.type = 'D'
    ORDER BY bs.backup_finish_date DESC
) AS bs
ORDER BY 
    p.ShrunkenHead,
    p.StartTime DESC;

With that, you can get some idea of how the database backup size changed. I'm including regular and compressed sizes, just in case.