Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series) Expand / Collapse
Author
Message
Posted Friday, April 8, 2011 8:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 72, Visits: 286
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?

Post #1090685
Posted Friday, April 22, 2011 10:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 72, Visits: 286
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
Post #1097528
Posted Friday, January 6, 2012 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:48 AM
Points: 3, 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
Post #1231391
Posted Friday, January 6, 2012 6:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:19 PM
Points: 55, Visits: 291
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

Post #1231422
Posted Monday, January 9, 2012 12:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:48 AM
Points: 3, Visits: 25
Awesome!
Thanks a lot, that works great

/Jon
Post #1232224
Posted Wednesday, September 12, 2012 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 11:25 AM
Points: 176, Visits: 406
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
Post #1358084
Posted Thursday, September 13, 2012 12:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:48 AM
Points: 3, 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


Post #1358351
Posted Friday, September 14, 2012 7:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 11:25 AM
Points: 176, Visits: 406
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?



Post #1359259
Posted Tuesday, December 4, 2012 10:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:24 AM
Points: 467, Visits: 1,681
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
Post #1392611
Posted Thursday, November 7, 2013 3:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:26 PM
Points: 1, 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.
Post #1512481
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse