How to track DBCC commands issued in SQL Server using default trace

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.

DBCC_Tracker

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s