Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » How to Create and Start SQL Server Trace...
Add to briefcase
23 posts, Page 2 of 3
««123»»

How to Create and Start SQL Server Trace Automatically Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Perry Whittle
Posted Thursday, March 19, 2009 11:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165
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
Lowell
Posted Thursday, March 19, 2009 11:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 11,648, Visits: 27,764
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
Perry Whittle
Posted Thursday, March 19, 2009 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165
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
FreeHansje
Posted Wednesday, June 03, 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 06, 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
ALZDBA
Posted Wednesday, June 03, 2009 5:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049
Attached you'll find a version I use for quick and dirty CPR-trace


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

- 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 (86 views, 35.17 KB)
Post #728060
FreeHansje
Posted Wednesday, June 03, 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 06, 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
Lowell
Posted Wednesday, June 03, 2009 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 11,648, Visits: 27,764
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
ALZDBA
Posted Thursday, June 04, 2009 12:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049
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

     Jul 13

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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:40 PM
Points: 193, Visits: 210
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
Richard M.
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: 2 days ago @ 11:42 AM
Points: 1,058, Visits: 2,575
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
23 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.