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 Thursday, March 19, 2009 11:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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"
Post #679674
Posted Thursday, March 19, 2009 11:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--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 #679689
Posted Thursday, March 19, 2009 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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"
Post #679787
Posted Wednesday, June 3, 2009 5:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
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
Post #728055
Posted Wednesday, June 3, 2009 5:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 6,748, Visits: 8,545
Attached you'll find a version I use for quick and dirty CPR-trace


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 Attachments 
Start SQL-trace to file.sql.txt (97 views, 35.17 KB)
Post #728060
Posted Wednesday, June 3, 2009 6:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Tnx.
Found some similar stuff myself, but your's more detailed & extended.
Tnx again.



Greetz,
Hans Brouwer
Post #728086
Posted Wednesday, June 3, 2009 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
ALZDBA (6/3/2009)
Attached you'll find a version I use for quick and dirty CPR-trace

ALZDBA the version you posted has all the CrLf stripped out, making it difficult to test, since we have to reformat manually; can you edit it or repaste it?


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 #728111
Posted Thursday, June 4, 2009 12:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 6,748, Visits: 8,545
Lowell (6/3/2009)
ALZDBA (6/3/2009)
Attached you'll find a version I use for quick and dirty CPR-trace

ALZDBA the version you posted has all the CrLf stripped out, making it difficult to test, since we have to reformat manually; can you edit it or repaste it?


You need to change the file extention !
(just remove the .txt and keep the .SQL)

If you then open it with ssms, it will be formatted OK.


(I downloaded it from this thread, removed the .txt and it appeared ok in SSMS)


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 #728694
Posted Friday, July 10, 2009 12:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:59 AM
Points: 229, Visits: 252
Hello,

I want to profile and write to a table.
But in the script generated by the profiler, in the export funcionality, the following code is written:

-- Writing to a table is not supported through the SP's

Do you know some way to enable the trace without using the profiler, save the data in tables, instead of in files? I really prefer to save in tables.

Thanks in advance
DBA Cabuloso



________________
DBA Cabuloso
Lucas Benevides
Post #751338
Posted Friday, July 10, 2009 1:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 1,093, Visits: 2,620
DBA Cabuloso (7/10/2009)Do you know some way to enable the trace without using the profiler, save the data in tables, instead of in files? I really prefer to save in tables.



That's where the
SELECT * INTO temp_trc
FROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)

comes in play... reading the trace files after the fact and that way you can also insert them into a table for analysis.




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #751370
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse