Red Gate SQL Prompt DBCC TRACEON

Red Gate SQL Compare generates hundreds of these messages in the SQL Server error logs:

DBCC TRACEON 3604, server process ID (SPID). This is an informational message only; no user action is required.
DBCC TRACEOFF 3604, server process ID (SPID). This is an informational message only; no user action is required.

Using a web search, it seems like the solution is to turn off the check box for decrypt encrypted objects. However, it doesn’t remove these completely.

I’m disappointed that RedGate doesn’t have an article explaining why these messages are necessary. Instead, they have people search through the forums, which don’t seem incredibly helpful.

It’s a really good tool though, so it’s not a huge price to pay.

The way to find the computers that are generating these messages is to use SQL Profiler.  Use a standard trace, then uncheck all the events other than “SQL:BatchCompleted”.

In the Column Filters, put these in the following:

  • ApplicationName -> like -> %Red Gate%
  • TextData -> like -> %DBCC%

Even better, use a server-side trace. Here is a script to create that trace:

/****************************************************/
/* Created by: SQL Server 2012 Profiler */
/* Date: 02/12/2014 02:32:38 PM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, N'B:\Traces\DBCCTraceON', @maxfilesize, NULL, 3
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%DBCC TRACE%'
exec sp_trace_setfilter @TraceID, 10, 0, 6, N'%Red Gate%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go
Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

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