Logging of DBCC Commands

Question

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

Answer 1

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

Answer 2

An Event Notification can be used to both monitor for DBCC command events and respond to them. The script below creates all the necessary objects, which I would recommend be created in a separate database.

I've added comments here and there where appropriate. Most of the script can probably be left intact, except for the dbo.ReceiveDbccCommand stored proc, which you will want to tailor for your needs. If this solution doesn't fit your needs, you can simply drop the database.

--All of the objects can be created in a separate database.
IF DB_ID('DbaEvents') IS NULL
    CREATE DATABASE DbaEvents;

ALTER DATABASE DbaEvents
SET ENABLE_BROKER;

USE DbaEvents
GO

--Drop objects first before trying to create them (in specific sequence).
IF EXISTS (
    SELECT * 
    FROM sys.server_event_notifications 
    WHERE name = 'enDbccCommand'
)
    DROP EVENT NOTIFICATION enDbccCommand
    ON SERVER
GO

IF EXISTS (
    SELECT *
    FROM sys.services
    WHERE name = 'svcDbccCommandNotification'
)
    DROP SERVICE svcDbccCommandNotification;
GO

IF EXISTS (
    SELECT *
    FROM sys.service_queues
    WHERE name = 'queDbccCommandNotification'
)
BEGIN
    ALTER QUEUE dbo.queDbccCommandNotification 
    WITH STATUS = OFF;

    DROP QUEUE queDbccCommandNotification;
END
GO

IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES r
    WHERE r.ROUTINE_SCHEMA = 'dbo' AND r.ROUTINE_NAME = 'ReceiveDbccCommand'
)
    DROP PROCEDURE dbo.ReceiveDbccCommand 
GO

--Create a queue just for DBCC Command events.
CREATE QUEUE queDbccCommandNotification;

--Create a service just for DBCC Command events.
CREATE SERVICE svcDbccCommandNotification
ON QUEUE queDbccCommandNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

-- Create the event notification for DBCC Command events on the service.
CREATE EVENT NOTIFICATION enDbccCommand
ON SERVER
WITH FAN_IN
FOR AUDIT_DBCC_EVENT
TO SERVICE 'svcDbccCommandNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveDbccCommand
/******************************************************************************
* Name     : dbo.ReceiveDbccCommand
* Purpose  : Handles DBCC events (activated by QUEUE queDbccCommandNotification)
* Inputs   : None
* Outputs  : None
* Returns  : Nothing
******************************************************************************
* Change History
*   ??/??/2015  DMason  Created
******************************************************************************/
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MsgBody XML

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION

        -- Receive the next available message FROM the queue
        WAITFOR (
            RECEIVE TOP(1) -- just handle one message at a time
                @MsgBody = CAST(message_body AS XML)
                FROM queDbccCommandNotification
        ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
        -- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END 
        ELSE
        BEGIN
            DECLARE @Login SYSNAME
            DECLARE @Success BIT
            DECLARE @Cmd VARCHAR(1024)
            DECLARE @MailBody NVARCHAR(MAX)
            DECLARE @Subject NVARCHAR(255)

            --Get some pertinent data from the XML.
            SET @Login = @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)' );
            SET @Success = @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(8)' ); 
            SET @Cmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(1024)');


            --OPTIONAL: you can check which DBCC command occurred and proceed conditionally.
            --IF @Cmd COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%FREEPROCCACHE%'
            --BEGIN

            --Send email/alert.
            SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )    
            SET @MailBody = 
                '<table border="1">' +
                '<tr><td>Server Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Start Time</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                '<tr><td>Session Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Login Name</td><td>' + @Login + '</td></tr>' + 
                '<tr><td>Windows Domain\User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(256)') + '\' +
                    @MsgBody.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(256)') + '</td></tr>' +  
                '<tr><td>DB User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Host Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                '<tr><td>Application Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Command Succeeded</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(8)' ) + '</td></tr>' + 
                '</table><br/>' +
                '<p><b>Text Data:</b><br/>' + REPLACE(@Cmd, CHAR(13) + CHAR(10), '<br/>') +'</p><br/>'

            EXEC msdb.dbo.sp_send_dbmail 
                @recipients = '[email protected]', 
                @subject = @Subject,
                @body = @MailBody,
                @body_format = 'HTML',
                @exclude_query_output = 1

            --OPTIONAL: log to table.
            --INSERT INTO dbo.SomeTable(EventDataXml)
            --VALUES (@MsgBody)

            /*
                Commit the transaction.  At any point before this, we 
                could roll back -- the received message would be back 
                on the queue AND the response wouldn't be sent.
            */
            COMMIT TRANSACTION
        END
    END
END
GO

ALTER QUEUE dbo.queDbccCommandNotification 
WITH 
    STATUS = ON, 
    ACTIVATION ( 
        PROCEDURE_NAME = dbo.ReceiveDbccCommand, 
        STATUS = ON, 
        MAX_QUEUE_READERS = 1, 
        EXECUTE AS OWNER);
GO