How to Create and Start SQL Server Trace Automatically

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to Create and Start SQL Server Trace...

23 posts, Page 1 of 3

1 2 3 »»»

How to Create and Start SQL Server Trace Automatically

Rate Topic

Display Mode

Topic Options

Author

Message

dakshinamurthy-655138

Posted Friday, September 26, 2008 6:41 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 329, Visits: 883

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

Post #576752

GRE (Gethyn Ellis)

Posted Friday, September 26, 2008 7:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

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 Ellis

gethynellis.com

Post #576838

Perry Whittle

Posted Friday, September 26, 2008 9:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

dakshinamurthy (9/26/2008)

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

;)

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

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #576970

dakshinamurthy-655138

Posted Saturday, September 27, 2008 1:25 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 329, Visits: 883

Hi Perry Whittle,

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

With Regards
Dakshina

Post #577273

ALZDBA

Posted Saturday, September 27, 2008 5:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 6,862, Visits: 8,049

I think my little article regarding sqlserver and SOx can help out.

http://www.sqlservercentral.com/articles/Security/3203/

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #577292

Perry Whittle

Posted Tuesday, September 30, 2008 6:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

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" Wink

Post #578251

dakshinamurthy-655138

Posted Tuesday, December 09, 2008 10:53 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 329, Visits: 883

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

Post #616680

ALZDBA

Posted Wednesday, December 10, 2008 2:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #616762

sivashankar-350147

Posted Monday, March 16, 2009 4:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 5:29 AM
Points: 32, Visits: 57

Hi Daksh,

Its very usefull , thanks man..:)

Regards
Siv

Post #676347

Lowell

Posted Thursday, March 19, 2009 10:28 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 11,630, Visits: 27,698

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #679636

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions