If you are a production DBA and responsible for your SQL Server box, sometimes, you may need to know certain information like who run the DBCC commands or when did it triggered or How did it triggered etc.

This post provides a quick way to identify those information using the below query to track DBCC commands that were issued in a SQL Server.

The query uses FN_TRACE_GETTABLE function to read information from the default trace in SQL Server.

SELECT
	T.DatabaseName,
	T.TextData,
	T.HostName,
	T.ApplicationName,
	T.LoginName,
	T.StartTime,
    CASE T.IsSystem
		WHEN 1 THEN 'YES'
		ELSE 'NO'
		END AS IsSystem
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150), 
	( SELECT TOP 1 f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2
		)), DEFAULT) T
WHERE T.EventClass = 116

The above code shows the way to track *ONLY* DBCC commands(EventClass = 116). However, you may be able to change the Query to filter the EVENTCLASS to identify other Audit events.

To add, How do you identify your default trace?

SELECT * FROM sys.traces WHERE is_default = 1

Alternatively,

SELECT *, f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2

If you want to explore on other events, you can collect the Event trace id from the below query and replace the EventClass filter value in the above query.

Select * From sys.trace_events