September 26, 2008 at 6:41 am
Hi,
I would like to start and store the sql profiler trace in a location, but it should be automated, i mean to say instead of starting the sql profiler for a particular db manually is there any way where in i can schedule the tracing to start every day and stop. So that we can read the sql server trace later whenever we need.
With Regards
Dakshina
September 26, 2008 at 7:38 am
What you can is setup your trace using the Profiler GUI, you can then script out the T-SQL needed to run the profiler and use it in a sql server job, and schedule as and when you need it.
To get the script the option can found under in the profiler GUI
You may need seperate job/step to stop the trace to...
Look up the neccessary SP in BOL.
Gethyn Elliswww.gethynellis.com
September 26, 2008 at 9:47 am
dakshinamurthy (9/26/2008)
Hi,I would like to start and store the sql profiler trace in a location, but it should be automated, i mean to say instead of starting the sql profiler for a particular db manually is there any way where in i can schedule the tracing to start every day and stop. So that we can read the sql server trace later whenever we need.
With Regards
Dakshina
Hi Daksina
the 4 stored procedures you need are
SP_TRACE_CREATE
SP_TRACE_SETEVENT
SP_TRACE_SETFILTER
SP_TRACE_SETSTATUS
also the functions
fn_trace_gettable
fn_trace_getinfo
to generate a trace definition use the following syntax
declare @traceidout int
declare @maxfilesize bigint
declare @on bit
set @on = 1
set @maxfilesize = 50
exec sp_trace_create @traceidout output, 2, N'D:\Trace\mytrace', @maxfilesize, NULL
exec sp_trace_setevent @traceidout, 12, 1, @on
exec sp_trace_setevent @traceidout, 12, 3, @on
change @maxfilesize to whatever value you require (its in MB)
Dont append the .TRC to the path above it does it for you.
refer to BOL for all trace events and columns
set a filter using the following syntax
exec sp_trace_setfilter @traceidout, 3, 0, 0, 7
use the following to get your trace details and ID
select * from ::fn_trace_getinfo(default)
use the following to start, stop and close the trace
(must stop a trace before it can be closed. Must be closed before you can access the file)
exec sp_trace_setstatus TRACEID, 1 --start trace
exec sp_trace_setstatus TRACEID, 0 --stop trace
exec sp_trace_setstatus TRACEID, 2 --close trace
finally to output to a table stop and close the trace then use the following syntax
SELECT * INTO temp_trc
FROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)
You can create SQL jobs and schedule them using the code above. As it runs server side there are no I\O nasties that a client would generate and you can schedule it at will
😎 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 27, 2008 at 1:25 am
Hi Perry Whittle,
Thanks a lot, it worked and now i am able use sql trace automatically.
With Regards
Dakshina
September 27, 2008 at 5:08 am
I think my little article regarding sqlserver and SOx can help out.
http://www.sqlservercentral.com/articles/Security/3203/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 30, 2008 at 6:48 am
ALZDBA (9/27/2008)
I think my little article regarding sqlserver and SOx can help out.http://www.sqlservercentral.com/articles/Security/3203/
its ok but i think he just wanted a quick and dirty guide on the usage of the SP's and functions 😉
I think they get a lot of peoiple confused first time round
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 9, 2008 at 10:53 pm
I would like to know how to AutoTrace for a specific database, if i have 5 databases in the server and i need to enable the trace or autotrace to only 3 datbases which i like. So how do i go with it. Please help
With Regards
Dakshin
December 10, 2008 at 2:04 am
easyest way is to use sql server profiler.
Create your trace with all filters (db) you want and script it.
Then launch it in a startup job or procedure.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 16, 2009 at 4:21 am
Hi Daksh,
Its very usefull , thanks man..:)
Regards
Siv
March 19, 2009 at 10:28 am
Perry your code example was very helpful for me today, as I wanted to create a DML trace to be a companion to the existing DDL default trace.
so I played around with your example a bit, and saw your example trace enabled just two columns, the TextData and DbId;
i took it a step further, and enabled columns i thought might be useful for auditing SQL statements...using the sp_trace_setevent command, I added
everything I could find related to the user/login performing the action, and also everything i could find related to performance times,
it worked exactly as I expected, and could help track down issues in the future.
my question is really this; do you know if there is any impact of just simply enabling all 64 columns in the trace, instead of a select group of my 12 columns I thought were useful.
I'll find out the hard way, since I'll leave it enabled to see how well it does, but it kind of seems like a monotonously wasteful timewise to explicitly call sp_trace_setevent for all 64 columns, instead of having all columns enabled by default.
That's what made me think there must be a reason to enable some columns and not all columns.
Lowell
March 19, 2009 at 11:00 am
Lowell (3/19/2009)
That's what made me think there must be a reason to enable some columns and not all columns.
Hi Lowell
you only really want to capture the events\columns you require otherwise reading the trace can be a little tedious. To capture a column for an event you need to use the setevent SP for each column. I showed the 2 columns for event 12 as an example, but you could specify any valid event\column (be aware not all events use all columns). Its really down to the events\columns you want to see.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 19, 2009 at 11:14 am
yeah, for event 12 SQL:BatchCompleted, even when everything is enabled, there is a lot of null columns, i can see that you want to capture just certain data depending on the event you are watching, but the level of granularity seemed a bit tedious to invoke.
Thanks!
Lowell
March 19, 2009 at 12:42 pm
Lowell (3/19/2009)
the level of granularity seemed a bit tedious to invoke.Thanks!
its very rare though you would capture every column for an event 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 3, 2009 at 5:18 am
easyest way is to use sql server profiler.
Create your trace with all filters (db) you want and script it.
Then launch it in a startup job or procedure.
Got that part, but the trace stops almost immediately. Since the job starts the trace-script, should I add the StopTrace and StartTrace procedures?
Greetz,
Hans Brouwer
June 3, 2009 at 5:33 am
Attached you'll find a version I use for quick and dirty CPR-trace 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply