Create server side trace.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Create server side trace.

Create server side trace.

Rate Topic

Display Mode

Topic Options

Author

Message

Stephen Knott

Posted Friday, April 17, 2009 7:40 AM

Valued Member

Group: General Forum Members
Last Login: Friday, July 27, 2012 1:40 AM
Points: 69, Visits: 100

Hi,

I am trying to use an idea from Gail's idea found at..

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

I have created the follow script from profiler..

Running it give me error :

12 - which I believe is File not created.

I have checked, there are no other traces running, and the SQL service has access to the folder. I have tried creating this even in the folder where the data files live, to ensure that it had access.

Is there anything else I can check ?

Below is my script :

/****************************************************/
/* Created by: SQL Profiler */
/* Date: 16/04/2009 12:29:20 */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500
declare @OutputFileName nvarchar(200)
declare @EndTime datetime

SET @OutputFileName = 'D:\Trace\ServerTrace' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

SET @EndTime = DATEADD(mi,30,getdate())

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, OutputFileName, @maxfilesize, @EndTime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft Dynamics NAV client'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Post #699417

Jack Corbett

Posted Friday, April 17, 2009 8:16 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 10,613, Visits: 11,954

Well if the code you posted is the exact code you used then your problem is that you are missing the '@' sign before OutPutFileName in the sp_trace_create call here's your code:

exec @rc = sp_trace_create @TraceID output, 0, OutputFileName, @maxfilesize, @EndTime

And here's what it should be:

exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @EndTime

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 #699458

Stephen Knott

Posted Friday, April 17, 2009 8:20 AM

Valued Member

Group: General Forum Members
Last Login: Friday, July 27, 2012 1:40 AM
Points: 69, Visits: 100

Ok... now I feel like an idiot...

Thank you.. not sure how I deleted the @..

Thanks again

Post #699464

Jack Corbett

Posted Friday, April 17, 2009 8:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 10,613, Visits: 11,954

Like we all haven't done the same thing a hundred times. That's what the extra set of eyes is for.

Post #699478

« Prev Topic | Next Topic »

Permissions