Who’s Been Running DBCC Commands?

sp_Blitz will warn you if someone’s been running unusual DBCC commands. We’re not worried about CHECKDB – in fact, we’re excited when people do that – but rather, we’re worried about folks doing things they probably shouldn’t be doing in production.

To find it, we’re filtering through this data from your default trace:

DECLARE @TraceFileName NVARCHAR(400);
SELECT TOP 1 @TraceFileName = path
  FROM sys.traces
  WHERE is_default = 1;
SELECT *
  FROM sys.fn_trace_gettable(@TraceFileName, DEFAULT) t
  WHERE (
          t.EventClass = 22
          AND t.Severity >= 17
          AND t.StartTime > DATEADD(dd, -30, GETDATE())
      )
      OR (
             t.EventClass IN ( 92, 93 )
             AND t.StartTime > DATEADD(dd, -30, GETDATE())
             AND t.Duration > 15000000
         )
      OR (t.EventClass IN ( 94, 95, 116 ));

DECLARE @TraceFileName NVARCHAR(400);

SELECT TOP 1 @TraceFileName = path

FROM sys.traces

WHERE is_default = 1;

SELECT *

FROM sys.fn_trace_gettable(@TraceFileName, DEFAULT) t

WHERE (

t.EventClass = 22

AND t.Severity >= 17

AND t.StartTime > DATEADD(dd, -30, GETDATE())

)

OR (

t.EventClass IN ( 92, 93 )

AND t.StartTime > DATEADD(dd, -30, GETDATE())

AND t.Duration > 15000000

)

OR (t.EventClass IN ( 94, 95, 116 ));

That query will return even more than just DBCC commands – but I’m not filtering it here because you’re probably interested in these other things that are happening on your server, too.

That query’s only the starting point for reading the default trace. There are more things you can do with it, like reading more files (not just the top 1), extracting them into tables, or filtering for specific users or databases or events. To learn more: