January 13, 2009 at 5:47 am
This isn't really about performance tuning. But, since I'm here...
I have a trace that I want to start as a job in SQL Server. To save time and effort, I'd like to script out my Template to make use of the sp_trace_setevent stored procedure. Otherwise, I'll be spending a lot of time writing out these procs.
Does anyone know how to take an existing template and write it out as a script using the Trace procs?
If not, does anyone know, when creating a trace using sp_trace_create, what events & columns are automatically "On" as opposed to the ones that are "Off"? I don't want to waste time writing the event proc to turn off columns & events that are already "Off".
Thanks in advance!
January 13, 2009 at 6:03 am
Brandie - I think the following link will give you the information you are looking for;
The title is a bit misleading as it does talk about getting the script out of profiler. Comes in handy. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 13, 2009 at 6:09 am
Thanks for the link, David! I'll check it out ASAP.
January 13, 2009 at 6:20 am
Should have put this in originally so the information could be on this thread as well. From the link above;
Briefly start the trace and then pause it. Goto File->Export->Script Trace Definition and pick your DB version, and save to a file.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 13, 2009 at 6:38 am
You can define the trace using the profiler in the regular way. After you define it, you can export the trace’s definition to a text file by going to file?export?Script trace definition. Notice that you’ll have to modify the file a bit (location of the data file, some options on the data files etc’ but not the event and filters).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 13, 2009 at 6:44 am
You guys are describing how to save the trace definition, not the template. Is that what you need Brandie?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 6:52 am
I think Brandie wanted to script out the template, which I believe this will accomplish. Hope so anyway, hate to answer the wrong question. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 13, 2009 at 7:29 am
David's information worked.
I had to create a trace in Profiler with the Template in question (it was a homemade Template). I paused it and scripted it out. Looking at the SQL, it appears to have turned on all the appropriate Events & Columns, including my Column Filters.
AND with an EndTime. YEAH!!!!!
Gotta love it. @=) Of course, if you don't choose the correct information in Profiler, it will script out the wrong items.
Thanks, David. That was a really big help.
January 13, 2009 at 8:29 am
DavidB (1/13/2009)
Briefly start the trace and then pause it. Goto File->Export->Script Trace Definition and pick your DB version, and save to a file.
The reason I asked is because, what you describe is exporting the Trace Definition. To create a template and export it, you use the Template menu choice.
A trace definition creates a type of trace, usually based on a template, which you can then run where needed. A template defines a set of criteria for the trace from which you can create trace definitions.
That's why I asked, I saw everyone giving Brandie trace definitions, which it seems is what she was looking for, not templates.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 10:37 am
Actually, Grant, I didn't know about this Template scripter. Could you give me that too?
I was going to make due with what I had, but I'd like to look at what you're talking about just to make sure.
EDIT: Truth be told, at this moment, I'm not sure what question I'm asking. @=)
Thanks,
January 13, 2009 at 10:50 am
Brandie Tarvin (1/13/2009)
Actually, Grant, I didn't know about this Template scripter. Could you give me that too?I was going to make due with what I had, but I'd like to look at what you're talking about just to make sure.
EDIT: Truth be told, at this moment, I'm not sure what question I'm asking. @=)
Thanks,
It's right there in the template menu. You can create a template and then export it, move it and reimport it. Easy-peasy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 11:34 am
However, the template file will be usable in Profiler. If you script it out then you can run it via SSMS or a job and still have the same configuration and outputs. So, best of both worlds. 🙂
I know, you say Tom-A-to, I say Tom-Ah-to.
Sorry, not a big fan of the profiler UI....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 13, 2009 at 11:49 am
DavidB (1/13/2009)
However, the template file will be usable in Profiler. If you script it out then you can run it via SSMS or a job and still have the same configuration and outputs. So, best of both worlds. 🙂I know, you say Tom-A-to, I say Tom-Ah-to.
Sorry, not a big fan of the profiler UI....
I know what you mean. I never use it in Production, server side scripts only. But in dev and the other environments, it's a quick way to pull some information out, so knowing how to use it adds to the toolbox.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply