patternsqlMinor

Logging of DBCC Commands

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

commandsloggingdbcc

Problem

Is there are way to trace if DBCC commands are being run against a SQL Server database?

Specifically I am looking to see if the following 2 commands have been run at any point?

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

The reason is that the cache keeps emptying and I am pretty sure there isn't a great amount of memory pressure on the server.

I would really like to rule out the fact that this is being done by someone and really is a genuine issue I need to investigate and/or add more memory to the server.

Thanks in advance
David

Solution

Here is an extended event session to find any DBCC calls, that have not already occured. From your question it looks like you may have wanted to find ones in the past? Like others have alluded to there should be info in the logs on cache clears, and should read as follows (at least on SQL2014):

Date,Source,Severity,Message
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

For the future you could use this Extended Event definition.

CREATE EVENT SESSION [DBCC] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(0)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%'))), ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%')))
GO
-- Uncomment if you want to output to a target file and change the file path
--ALTER EVENT SESSION [DBCC] ON SERVER 
--ADD TARGET package0.event_file(SET filename=N'K:\Backup\DBCC_XE',max_rollover_files=(2))
--GO

ALTER EVENT SESSION [DBCC] ON SERVER STATE = START
GO

-- Don't forget to turn it off
ALTER EVENT SESSION [DBCC] ON SERVER STATE = STOP
GO

Will get you some information like this (my user and server name are redacted):

If you want a trace (SQL Profiler) definition I could probably throw one of those together too.

Hope this helps.

Cheers

Code Snippets

Date,Source,Severity,Message
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

CREATE EVENT SESSION [DBCC] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(0)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%'))), ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%')))
GO
-- Uncomment if you want to output to a target file and change the file path
--ALTER EVENT SESSION [DBCC] ON SERVER 
--ADD TARGET package0.event_file(SET filename=N'K:\Backup\DBCC_XE',max_rollover_files=(2))
--GO


ALTER EVENT SESSION [DBCC] ON SERVER STATE = START
GO

-- Don't forget to turn it off
ALTER EVENT SESSION [DBCC] ON SERVER STATE = STOP
GO

Context

StackExchange Database Administrators Q#146457, answer score: 6

Revisions (0)

No revisions yet.