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 ««1234»»»

Set Up And Schedule a Server Side Trace Expand / Collapse
Author
Message
Posted Thursday, December 2, 2010 7:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 15,518, Visits: 27,900
Jack Corbett (12/2/2010)
Great article again Grant. I just prefer to use sys.traces over fn_trace_getinfo. I think it gives an easier to decipher set of information.

Hmm, I'm going to have to play with the stop time because I thought it deleted the trace definition...


I did too. If you get to it before I do (at work, can't play at the moment) please post it.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1029236
Posted Thursday, December 2, 2010 7:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 15,518, Visits: 27,900
PAH-440118 (12/2/2010)
What is the command to delete to the trace definition?


Right here in the Books Online:

1.Execute sp_trace_setstatus by specifying @status = 0 to stop the trace.

2.Execute sp_trace_setstatus by specifying @status = 2 to close the trace and delete its information from the server.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1029243
Posted Thursday, December 2, 2010 7:17 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 9, 2014 7:16 AM
Points: 634, Visits: 1,526
Grant Fritchey (12/2/2010)


Writing to file is much, much faster than writing to a database, so yes, it does make a difference. Also, is that database on the same server you're monitoring? That can be a problem, adding overhead where none should exist.


But of course it's on the same server :) Since I'm going to change process around anyway, I'll change it to create the flat files and import then into the database afterwards. I was just being lazy to save a step as it hasn't yet caused a problem. I certainly don't want to be the cause of problems, so thank-you for your response.

Cindy
Post #1029245
Posted Thursday, December 2, 2010 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
I haven't noticed it would not cleanup the trace def in systraces after the end time.

Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing

So in my instances they get cleaned up.

edited
You can find my setup script in the attachement it was to long to just post in the reply


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_trace.txt (9 views, 18.50 KB)
Post #1029259
Posted Thursday, December 2, 2010 7:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:18 AM
Points: 83, Visits: 233
I just want to say Thanks for this article. When setting up server side trace scripts last week (for the first time) I was annoyed at how cumbersome and error-prone creating the script was. Learning that it can be scripted out from Profiler will save me lots of time and pain next time.
Post #1029274
Posted Thursday, December 2, 2010 7:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 15,518, Visits: 27,900
CavyPrincess (12/2/2010)
Grant Fritchey (12/2/2010)


Writing to file is much, much faster than writing to a database, so yes, it does make a difference. Also, is that database on the same server you're monitoring? That can be a problem, adding overhead where none should exist.


But of course it's on the same server :) Since I'm going to change process around anyway, I'll change it to create the flat files and import then into the database afterwards. I was just being lazy to save a step as it hasn't yet caused a problem. I certainly don't want to be the cause of problems, so thank-you for your response.

Cindy


Of course. Happy to help. Also, tracking down Brad McGehee's excellent book will help even more.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1029280
Posted Thursday, December 2, 2010 1:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
ALZDBA (12/2/2010)
I haven't noticed it would not cleanup the trace def in systraces after the end time.

Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing

So in my instances they get cleaned up.

edited
You can find my setup script in the attachement it was to long to just post in the reply


I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1029534
Posted Thursday, December 2, 2010 6:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 15,518, Visits: 27,900
Jack Corbett (12/2/2010)
ALZDBA (12/2/2010)
I haven't noticed it would not cleanup the trace def in systraces after the end time.

Tested in SQL2000 / SQL2005 / SQL2008R2 CU4 .... ehm .... yes SQL2008 r1 is missing

So in my instances they get cleaned up.

edited
You can find my setup script in the attachement it was to long to just post in the reply


I did a quick test on SQL 2008 and the definition was cleaned up when the trace stopped. My test was simpler. I used Profiler to script a trace based on the standard template and set it to run for 5 minutes. After 5 minutes the only trace found in sys.traces is the default trace.


Ha! I was just sitting down to start testing this when I saw your post. Thanks. It's what I thought, but it never hurts to be sure.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1029603
Posted Thursday, December 2, 2010 10:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,229, Visits: 14,936
Great Stuff Grant.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1029645
Posted Thursday, December 2, 2010 10:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
Good stuff as always, Grant! Thanks for taking the time to write articles like this. This one should be in every DBA's quick reference guide.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1029650
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse