SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Dan Guzman / Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series) / Latest Posts

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

Dave Brooking — Tue, 04 Dec 2012 10:23:13 GMT

It looks like the Extended events for tracing are a new addition in 2012, does the following link help? [url]http://msdn.microsoft.com/en-us/library/ff878264.aspx[/url]Dave

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

DBA_Learner — Fri, 14 Sep 2012 07:13:59 GMT

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?

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

jb 60938 — Thu, 13 Sep 2012 00:10:48 GMT

HeyIf you didn't remove the line:[code="sql"]DECLARE @TraceID INT;[/code]You are declaring it, so I would check for misspellings, left out lines, or if you do a GO inbetween/Jon[quote][b]DBA_Learner (9/12/2012)[/b][hr]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[/quote]

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

DBA_Learner — Wed, 12 Sep 2012 09:56:55 GMT

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

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

jb 60938 — Mon, 09 Jan 2012 00:22:35 GMT

Awesome!Thanks a lot, that works great :-)/Jon

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

Dan Guzman-481633 — Fri, 06 Jan 2012 06:38:38 GMT

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 valueDECLARE @on bitSET @on = 1;SET @maxfilesize = 5;--step 1: create a new empty trace definitionEXEC sp_trace_create @traceid OUTPUT , @options = 2 , @tracefile = N'd:\LongRunningQueries' , @maxfilesize = @maxfilesize , @stoptime =NULL , @filecount = 2;-- step 2: add the events and columnsEXEC sp_trace_setevent @traceid = @TraceID , @eventid = 10 -- RPC:Completed , @columnid = 1 -- TextData , @on = @on; --include this column in trace

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

jb 60938 — Fri, 06 Jan 2012 05:31:49 GMT

HeyI am getting an error on adding the event[code="sql"]DECLARE @return_code INT;DECLARE @TraceID INT;DECLARE @maxfilesize BIGINT;SET @maxfilesize = 5;--step 1: create a new empty trace definitionEXEC sp_trace_create @traceid OUTPUT , @options = 2 , @tracefile = N'd:\LongRunningQueries' , @maxfilesize = @maxfilesize , @stoptime =NULL , @filecount = 2;-- step 2: add the events and columnsEXEC sp_trace_setevent @traceid = @TraceID , @eventid = 10 -- RPC:Completed , @columnid = 1 -- TextData , @on = 1;--include this column in trace[/code]Gives me [code]Msg 214, Level 16, State 3, Procedure sp_trace_setevent, Line 1Procedure expects parameter '@on' of type 'bit'.[/code]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

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

@SixStringSQL — Fri, 22 Apr 2011 10:43:35 GMT

[quote][b]Grubb (4/8/2011)[/b][hr]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?[/quote]Nevermind...It was truly a permissions issue. It's fun being a DBA without local admin access :(

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

@SixStringSQL — Fri, 08 Apr 2011 08:29:43 GMT

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?

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

Kartik M — Sat, 26 Mar 2011 09:13:01 GMT

Good Article.. Thanks for posting such helpful topics...

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

Anipaul — Wed, 23 Mar 2011 23:34:52 GMT

I am loving reading these articles... Thank you.Paul

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

Leonel Umaña Araya — Wed, 23 Mar 2011 09:42:39 GMT

Thanks Dan for the article. I'll hope for the next ones.

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

Dan Guzman-481633 — Wed, 23 Feb 2011 21:03:56 GMT

Hi, Toby.I also wish sp_trace_create had an option to overwrite an existing file. I'm not a big fan of file manipulation in T-SQL either. You might consider making a habit of the TRACE_FILE_ROLLOVER (option 2) with a file count of 2, even if you only need a single file for a one-time trace. This way, SQL Trace will add the incremental number to the file name if the base file name already exists and automatically delete the n-2 file.I'll cover trace data and file management later in this Stairway.

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

Toby White — Wed, 23 Feb 2011 13:10:06 GMT

Oh, there is one issue I wanted to discuss. It's not a problem with your article, but a problem I have struggled with in the past.I often set the file name like so:SELECT @File = Substring(Path,1,len(Path)-charindex('\',reverse(path))+1) + @FileFROM sys.tracesWHERE id = 1 Where the value of the @file variable is "Performance", "Duration" or something to that effect. The problem is that if the file already exists the sp_trace_create procedure fails. The two ways I have used to cope with this is check for existence with xp_fileexists and then either bail, or use xp_cmdshell programmatically to remove the file. I have never liked either of my options, and I wish there was a way to automatically overwrite the file if it exists on the sp_trace_create procedure.I suppose another option would be to check file existence, then append a number in a loop until I get one that doesn't exist. Do you have any thoughts or suggestions related to this problem?

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

Toby White — Wed, 23 Feb 2011 12:58:41 GMT

Excellent article. I also wanted to let you know about your additional predicate, but you've already identified it. I have written server side traces like this for years, but I always went against sys.trace_events and sys.trace_columns and was unaware of the sys.trace_event_bindings view. I have added the corrected version of your script to give me a better result set of possible events/columns to choose from.I try to use server side scripting exclusively even with dev/test environments. It seems a bit tedious at first, but since I already have something to start from there isn't that much to add/subtract usually. Further, the process of dealing with profiler is at least as tedious.Thanks for the article,Toby

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

sailendra — Mon, 21 Feb 2011 21:02:22 GMT

Thanks Dan. The updated query now returns the full resultset.

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

Dan Guzman-481633 — Mon, 21 Feb 2011 10:43:23 GMT

The query has an unintended predicate in the sys.trace_event_bindings JOIN clause. I thought this was removed from the final article draft but it looks like I erred. Below is the corrected version, which returns 3965 rows under SQL 2005. I'll get the query in the article corrected. Thanks for pointing this out.--list all possible trace events and columnsSELECT tcat.name AS EventCategoryName , tevent.name AS EventClassName , tcolumn.name AS EventColumn , tevent.trace_event_id AS EventID , tbinding.trace_column_id AS ColumnID , tcolumn.type_name AS DataTypeFROM sys.trace_categories AS tcat JOIN sys.trace_events AS tevent ON tevent.category_id = tcat.category_id JOIN sys.trace_event_bindings AS tbinding ON tbinding.trace_event_id = tevent.trace_event_id JOIN sys.trace_columns AS tcolumn ON tcolumn.trace_column_id = tbinding.trace_column_idORDER BY tcat.name , EventClassName , EventColumn ;

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

sailendra — Mon, 21 Feb 2011 04:08:28 GMT

I copied the script used to list all possible trace events and columns and executed on my sql server 2005 SP3 system. It returns only 14 rows (in other words not all possible categories - )Is there something I am missing here?The categories returned are:LocksScansSecurity AuditStored ProceduresTransactionsTSQLThanks for your help.

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

Dan Guzman-481633 — Mon, 14 Feb 2011 10:37:06 GMT

Comments posted to this topic are about the item [B]Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series)[/B]