Create server side trace.

  • 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

  • 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

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

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

    Thanks again

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply