Click here to monitor SSC
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
Dan Guzman-481633
Dan Guzman-481633
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 390
Comments posted to this topic are about the item Stairway to Server-side Tracing - Step 2: Creating a SQL Trace Using T-SQL (Stairway Series)
sailendra
sailendra
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 17
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:
Locks
Scans
Security Audit
Stored Procedures
Transactions
TSQL

Thanks for your help.



Dan Guzman-481633
Dan Guzman-481633
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 390
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 columns
SELECT 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 DataType
FROM 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_id
ORDER BY tcat.name ,
EventClassName ,
EventColumn ;
sailendra
sailendra
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 17
Thanks Dan. The updated query now returns the full resultset.



Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 639
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
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 639
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) + @File
FROM sys.traces
WHERE 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?
Dan Guzman-481633
Dan Guzman-481633
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 390
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.
Leonel Umaña Araya
Leonel Umaña Araya
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 28
Thanks Dan for the article. I'll hope for the next ones.


Leonel E. Umaña Araya
leo_umana@hotmail.com
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6491 Visits: 1407
I am loving reading these articles... Thank you.

Paul



Kartik M
Kartik M
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 219
Good Article.. Thanks for posting such helpful topics...

Thanks & Regards,
Kartik M Kumar..
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