SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Create and Start SQL Server Trace Automatically


How to Create and Start SQL Server Trace Automatically

Author
Message
dakshinamurthy-655138
dakshinamurthy-655138
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 1056
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
Gethyn Ellis
Gethyn Ellis
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1763 Visits: 2887
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 Ellisgethynellis.com
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26707 Visits: 17338
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
Cool Wink

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
dakshinamurthy-655138
dakshinamurthy-655138
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 1056
Hi Perry Whittle,


Thanks a lot, it worked and now i am able use sql trace automatically.

With Regards
Dakshina
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15699 Visits: 8967
I think my little article regarding sqlserver and SOx can help out.
http://www.sqlservercentral.com/articles/Security/3203/


Johan


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26707 Visits: 17338
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 Wink
I think they get a lot of peoiple confused first time round

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
dakshinamurthy-655138
dakshinamurthy-655138
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 1056
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
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15699 Visits: 8967
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
sivashankar-350147
sivashankar-350147
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 80
Hi Daksh,

Its very usefull , thanks man..Smile

Regards
Siv
Lowell
Lowell
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35885 Visits: 40249
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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