History of a manual database shrink event

Question

I performed a manual database shrink in SQL Server Management Studio, and I need to check how long it took to complete (running duration). Is there a specific log, system tables/views that I can check to find this kind of information please ?

Answer 1

it's not logged until you set up an extended event for it. However something is stored in default trace.

Query from here DBCC shrink file history

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%';
select * from #p