Unable to start the trace

Question

Post reply

Unable to start the trace

LearningDBA

SSCertifiable

Points: 6609
More actions
October 16, 2018 at 8:57 am

#365785

I have this piece of code which I executed but the trace isn't starting. Any idea?
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
exec @rc = sp_trace_create @TraceID output, 0, N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.FIRSTTRY\MSSQL\Log\FirstTryTrace.trc', @maxfilesize, NULL
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8ff968f5-5c01-42ac-b5ec-a17fe5f07a15'
-- 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
--must stop a trace before it can be closed. Must be closed before you can access the file
exec sp_trace_setstatus TRACEID, 1 --start trace
--exec sp_trace_setstatus TRACEID, 0 --stop trace
--exec sp_trace_setstatus TRACEID, 2 --close trace
select * from ::fn_trace_getinfo(default)
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50117 More actions · Answer 1

And what is the error you're getting, either when you run the code or in the error log?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What error are you getting?

Note that you don't have to run sp_trace_setstatus separately. It's part of the script already,

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LearningDBA SSCertifiable Points: 6609 More actions · Answer 3

jasona.work - Tuesday, October 16, 2018 9:05 AM
And what is the error you're getting, either when you run the code or in the error log?

When I run this command exec sp_trace_setstatus TRACEID, 1 --start trace

I get this message

Command run successfully
But when I run this select * from ::fn_trace_getinfo(default)
I see the value of property 1 is set to 0 which means trace isn't running.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

LearningDBA SSCertifiable Points: 6609 More actions · Answer 4

GilaMonster - Tuesday, October 16, 2018 9:08 AM
What error are you getting?
Note that you don't have to run sp_trace_setstatus separately. It's part of the script already,

I deleted the trace, ran it again like this but still no luck.

/****************************************************/

/* Created by: SQL Server vNext CTP2.0 Profiler */

/* Date: 10/16/2018 08:17:37 AM */

/****************************************************/

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 1024

exec @rc = sp_trace_create @TraceID output, 0, N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.FIRSTTRY\MSSQL\Log\FirstTryTrace.trc', @maxfilesize, NULL

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, 14, 10, @on

exec sp_trace_setevent @TraceID, 14, 11, @on

exec sp_trace_setevent @TraceID, 14, 6, @on

exec sp_trace_setevent @TraceID, 14, 12, @on

exec sp_trace_setevent @TraceID, 15, 11, @on

exec sp_trace_setevent @TraceID, 15, 6, @on

exec sp_trace_setevent @TraceID, 15, 10, @on

exec sp_trace_setevent @TraceID, 15, 12, @on

exec sp_trace_setevent @TraceID, 17, 10, @on

exec sp_trace_setevent @TraceID, 17, 11, @on

exec sp_trace_setevent @TraceID, 17, 6, @on

exec sp_trace_setevent @TraceID, 17, 12, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 13, 11, @on

exec sp_trace_setevent @TraceID, 13, 6, @on

exec sp_trace_setevent @TraceID, 13, 10, @on

exec sp_trace_setevent @TraceID, 13, 12, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8ff968f5-5c01-42ac-b5ec-a17fe5f07a15'

-- 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

--must stop a trace before it can be closed. Must be closed before you can access the file

exec sp_trace_setstatus 2, 1 --start trace

--exec sp_trace_setstatus 2, 0 --stop trace

--exec sp_trace_setstatus 2, 2 --close trace

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Again, you don't have to run the setstatus separately (and you probably shouldn't), the script already contains the line to start the trace.

So leave off your additions, and just run the code up to the GO. What errors do you get?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LearningDBA SSCertifiable Points: 6609 More actions · Answer 6

GilaMonster - Tuesday, October 16, 2018 9:15 AM
Again, you don't have to run the setstatus separately (and you probably shouldn't), the script already contains the line to start the trace.
So leave off your additions, and just run the code up to the GO. What errors do you get?

I ran the command, removed extra line, I got this in a message box

TraceID
2
But when I ran this
select * from ::fn_trace_getinfo(default)
I get 0 for traceID 2 which means it isn't running right?

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SELECT * FROM sys.traces (fn_trace_getinfo has been deprecated for years)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LearningDBA SSCertifiable Points: 6609 More actions · Answer 8

GilaMonster - Tuesday, October 16, 2018 11:31 AM
SELECT * FROM sys.traces (fn_trace_getinfo has been deprecated for years)

Thanks!

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]