SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series)


Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series)

Author
Message
@SixStringSQL
@SixStringSQL
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 389
Guys, after I create, run, stop, and delete the trace, I cannot ever seem to open the resulting file with Profiler: "Access denied". I'm assuming something still has it locked.

I am able to view it using fn_trace_gettable().

Am I missing a step?
@SixStringSQL
@SixStringSQL
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 389
Grubb (4/8/2011)
Guys, after I create, run, stop, and delete the trace, I cannot ever seem to open the resulting file with Profiler: "Access denied". I'm assuming something still has it locked.

I am able to view it using fn_trace_gettable().

Am I missing a step?


Nevermind...It was truly a permissions issue. It's fun being a DBA without local admin access Sad
jb 60938
jb 60938
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 25
Hey
I am getting an error on adding the event

DECLARE @return_code INT;
DECLARE @TraceID INT;
DECLARE @maxfilesize BIGINT;
SET @maxfilesize = 5;
--step 1: create a new empty trace definition
EXEC sp_trace_create
@traceid OUTPUT
, @options = 2
, @tracefile = N'd:\LongRunningQueries'
, @maxfilesize = @maxfilesize
, @stoptime =NULL
, @filecount = 2;
-- step 2: add the events and columns
EXEC sp_trace_setevent
@traceid = @TraceID
, @eventid = 10 -- RPC:Completed
, @columnid = 1 -- TextData
, @on = 1;--include this column in trace


Gives me

Msg 214, Level 16, State 3, Procedure sp_trace_setevent, Line 1
Procedure expects parameter '@on' of type 'bit'.


The trace is created, it's just the event selection that fails I guess (didn't try running it).
It's probably something simple, I just can't see it - any bright ideas?

By the way great article, reading it with great interest - only ever used the GUI profiler before (I haven't gotten to section 3 yet, but I hope there is a way of converting the GUI profiler trace setting to a script as mentioned in section 1)

/Jon
Dan Guzman-481633
Dan Guzman-481633
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 407
Hi, Jon.

Thanks for the positive feedback. I’m glad you found this Stairway informative.

It looks like you are using SQL 2005, which is a bit pickier regarding the bit data type parameter passed to sp_trace_setevent. Just declare and initialize a local variable of type bit for the parameter like the example below. You can pass the literal directly in SQL 2008 and later versions without the need for the local variable.

DECLARE @return_code INT;
DECLARE @TraceID INT;
DECLARE @maxfilesize BIGINT;
SET @maxfilesize = 5;

--SQL 2005 needs local variable for bit value
DECLARE @on bit
SET @on = 1;

SET @maxfilesize = 5;

--step 1: create a new empty trace definition
EXEC sp_trace_create
@traceid OUTPUT
, @options = 2
, @tracefile = N'd:\LongRunningQueries'
, @maxfilesize = @maxfilesize
, @stoptime =NULL
, @filecount = 2;

-- step 2: add the events and columns
EXEC sp_trace_setevent
@traceid = @TraceID
, @eventid = 10 -- RPC:Completed
, @columnid = 1 -- TextData
, @on = @on; --include this column in trace
jb 60938
jb 60938
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 25
Awesome!
Thanks a lot, that works great :-)

/Jon
DBA_Learner
DBA_Learner
SSC Eights!
SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)

Group: General Forum Members
Points: 943 Visits: 450
I am following the same way as in script..but receiving the folowing error on Step 2:

Must declare the scalar variable "@TraceID".

Any suggestions..do we need to declare any traceid value
jb 60938
jb 60938
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 25
Hey

If you didn't remove the line:
DECLARE @TraceID INT;



You are declaring it, so I would check for misspellings, left out lines, or if you do a GO inbetween
/Jon

DBA_Learner (9/12/2012)
I am following the same way as in script..but receiving the folowing error on Step 2:

Must declare the scalar variable "@TraceID".

Any suggestions..do we need to declare any traceid value

DBA_Learner
DBA_Learner
SSC Eights!
SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)

Group: General Forum Members
Points: 943 Visits: 450
Thanks for response. It is now working fine. But, I have a question.

Now in msdn sites for sp_trace_setevent / sp_trace_setfilter / sp_trace_setstatus , I see the following WARNING message.

/*
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead. */

So, can anyone explain me what is this Extended events mean?
Dave Brooking
Dave Brooking
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 1681
It looks like the Extended events for tracing are a new addition in 2012, does the following link help? http://msdn.microsoft.com/en-us/library/ff878264.aspx

Dave
majinbao
majinbao
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 30
Thank you very much for the post. great articles.

I have one question:

I can use trace to get who, when, from where took the database OFFLINE. but I can not get the whole information of who, when, from where took the database ONLINE.
the only information I can get is object:alert, but it doesn't tell me the TextData is null. I can not tell what happened to this database.

any solution? thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search