Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to Create and Start SQL Server Trace Automatically Expand / Collapse
Author
Message
Posted Friday, September 26, 2008 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:22 AM
Points: 330, Visits: 962
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
Posted Friday, September 26, 2008 7:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 AM
Points: 1,030, Visits: 2,794
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
Posted Friday, September 26, 2008 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 6,191, Visits: 13,340
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"
Post #576970
Posted Saturday, September 27, 2008 1:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:22 AM
Points: 330, Visits: 962
Hi Perry Whittle,


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

With Regards
Dakshina
Post #577273
Posted Saturday, September 27, 2008 5:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
I think my little article regarding sqlserver and SOx can help out.
http://www.sqlservercentral.com/articles/Security/3203/




Johan


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #577292
Posted Tuesday, September 30, 2008 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 6,191, Visits: 13,340
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"
Post #578251
Posted Tuesday, December 9, 2008 10:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:22 AM
Points: 330, Visits: 962
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
Posted Wednesday, December 10, 2008 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #616762
Posted Monday, March 16, 2009 4:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 9:29 AM
Points: 32, Visits: 78
Hi Daksh,

Its very usefull , thanks man..:)

Regards
Siv
Post #676347
Posted Thursday, March 19, 2009 10:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 12,889, Visits: 31,839
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse