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 Wednesday, December 01, 2010 10:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
Comments posted to this topic are about the item Set Up And Schedule a Server Side Trace

----------------------------------------------------
"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 #1029068
Posted Thursday, December 02, 2010 4:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:02 AM
Points: 68, Visits: 264
Thanks for this article. it was just what I needed and a feature I did not know about. I have been experimenting and it looks great however I get an error if the trace file already exists as a new one cannot be created (as you would expect). Is there a way of setting the output file name to be something that changes on each execution?
I have tried to create a variable for the file name and concatenating to this the date but this does not seem to work either.

Declare @SaveTo varchar(max)
Set @Saveto = 'C:\DirName\'+ (cast(Getdate()as char))

exec @rc = sp_trace_create @TraceID output, 2, @Saveto, @maxfilesize, @stoptime,NULL

I get an error of ‘Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.’

Any Suggestions?
Post #1029150
Posted Thursday, December 02, 2010 5:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 AM
Points: 6,997, Visits: 8,411
PAH-440118 (12/2/2010)
Thanks for this article. it was just what I needed and a feature I did not know about. I have been experimenting and it looks great however I get an error if the trace file already exists as a new one cannot be created (as you would expect). Is there a way of setting the output file name to be something that changes on each execution?
I have tried to create a variable for the file name and concatenating to this the date but this does not seem to work either.

Declare @SaveTo varchar(max)
Set @Saveto = 'C:\DirName\'+ (cast(Getdate()as char))

exec @rc = sp_trace_create @TraceID output, 2, @Saveto, @maxfilesize, @stoptime,NULL

I get an error of ‘Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.’

Any Suggestions?


You didn't pay attention when declaring your variable as a varchar(max).
Consider the implications for the engine.

the errormessage clearly states it expects a Nvarchar(256) , the easiest way is to actually do just that.

Declare @SaveTo Nvarchar(256)


This is the code I use to get easy to read trace file names:
set @TraceFileName = 'C:\temp\ALZDBA_SQL_Trace' 
+ '_' + replace(@@servername,'\','_') + '_'
+ replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') + '_'
-- + '.trc' is being added automatically


Another nice and useful article by Grant !


I also recommend reading Kalen Delaneys findings at http://sqlblog.com/blogs/kalen_delaney/archive/2007/06/29/did-you-know-a-6th-parameter-when-creating-a-trace.aspx where she states there is a parameter to take control on the number of rollover files.


I just learned about this at SQLPass2010


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 #1029160
Posted Thursday, December 02, 2010 5:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:01 AM
Points: 62, Visits: 308
Great article. I have been using this technique for a few months now and it saves tons of resources on the server, plus helps with my sleep when I need to trace a problem at 3am.

One thing to note though, when you run the script, it adds the trace. The trace will stop at the pre-defined stop time, but the trace definition will stay on the server. If you have this as a scheduled job to run every night or every day, it will keep adding new trace definitions. In my jobs, I just create a step to delete the trace after it completes.



Post #1029179
Posted Thursday, December 02, 2010 6:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
PAH-440118 (12/2/2010)
Thanks for this article. it was just what I needed and a feature I did not know about. I have been experimenting and it looks great however I get an error if the trace file already exists as a new one cannot be created (as you would expect). Is there a way of setting the output file name to be something that changes on each execution?
I have tried to create a variable for the file name and concatenating to this the date but this does not seem to work either.

Declare @SaveTo varchar(max)
Set @Saveto = 'C:\DirName\'+ (cast(Getdate()as char))

exec @rc = sp_trace_create @TraceID output, 2, @Saveto, @maxfilesize, @stoptime,NULL

I get an error of ‘Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.’

Any Suggestions?


Johan seems to have already answered your question for you. Let me know if you have any 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 #1029192
Posted Thursday, December 02, 2010 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
jason.spangler (12/2/2010)
Great article. I have been using this technique for a few months now and it saves tons of resources on the server, plus helps with my sleep when I need to trace a problem at 3am.

One thing to note though, when you run the script, it adds the trace. The trace will stop at the pre-defined stop time, but the trace definition will stay on the server. If you have this as a scheduled job to run every night or every day, it will keep adding new trace definitions. In my jobs, I just create a step to delete the trace after it completes.





Thanks.

You know that's not a problem I've run into. I'll have to check on it. Thanks for pointing it out.


----------------------------------------------------
"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 #1029194
Posted Thursday, December 02, 2010 6:46 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:48 PM
Points: 634, Visits: 1,515
Thanks for the great article. I create a baseline performance measure every so often on my servers, and this will simplify the process. I'm currently placing the output directly into the database via an ODBC connection. I'm sure I can change the script samples to do this, but just wondering if I should be doing this. Does it matter?

Thanks,
Cindy
Post #1029215
Posted Thursday, December 02, 2010 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 10,910, Visits: 12,548
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...




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 #1029227
Posted Thursday, December 02, 2010 7:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
CavyPrincess (12/2/2010)
Thanks for the great article. I create a baseline performance measure every so often on my servers, and this will simplify the process. I'm currently placing the output directly into the database via an ODBC connection. I'm sure I can change the script samples to do this, but just wondering if I should be doing this. Does it matter?

Thanks,
Cindy


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.


----------------------------------------------------
"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 #1029233
Posted Thursday, December 02, 2010 7:07 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:02 AM
Points: 68, Visits: 264
What is the command to delete to the trace definition?
Post #1029234
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse