Post reply

Automating the SQL Profiler

  • June 24, 2013 at 12:41 am

    #1626662

    Hi,

    I just edited the job and ran this below command:

    SELECT * INTO trace_table1 FROM ::fn_trace_gettable('C:\PWB_Test\T.trc', 1)

    The job went fine....and also deleted my T.trc file from folder...

    But how do I do for the rest of the files now...

    If I do change the value to 2 or 3 it says that, 'trace_table1' already exists... 🙁 job fails..

    Any suggestions for this one?

    Cheers

  • June 24, 2013 at 12:50 am

    #1626665

    Add a new first step to the job to check if the table exisits and only create it if it doesn't exist yet.

    Change the command to read the trace from "SELECT * INTO ... FROM " (which creates the table) to "INSERT ... SELECT * FROM " (to use the existing table).

    Use a combinations of SQL and commandprompt to get the tracefile with the lowest sequence number. Or you could use a staging table to hold the number and increase it each time the job has run. Or use some other logic to get the name of the file...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • June 24, 2013 at 12:57 am

    #1626667

    Hi,

    Didnt exactly get 🙁 am very very poor in programming and doing this for the first time...

    Can you please put up some logic and share it here 🙂

    Thank you for all your efforts..

    Cheers

  • June 24, 2013 at 1:09 am

    #1626668

    Take a look at the following links to get a file list:

    http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html

    Uses a undocumented feature that could be changed/removed by Microsoft without notice!!

    http://oakdome.com/programming/SQL_FileListing.php

    Uses the XP_CMDSHELL extended stored procedure and could be a security risk!!

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • June 24, 2013 at 2:59 am

    #1626694

    Running this 24\7 is potentially going to be massive amounts of data. I would have dedicated storage for the trace files.

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'EndTime', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'Reads', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'ClientProcessID', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'Writes', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'BinaryData', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'ApplicationName', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'CPU', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'LoginName', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'SPID', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'Duration', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'NTUserName', @on

    exec sp_trace_setevent @TraceID, 'RPC:Completed', 'StartTime', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'EndTime', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'Reads', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'TextData', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'ClientProcessID', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'Writes', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'NTUserName', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'ApplicationName', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'StartTime', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'CPU', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'LoginName', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'SPID', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'Duration', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'TextData', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'ClientProcessID', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'NTUserName', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'ApplicationName', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'StartTime', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'LoginName', @on

    exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'SPID', @on

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • June 24, 2013 at 5:12 am

    #1626743

    nivedita talukdar (6/24/2013)

    Hi,

    I just edited the job and ran this below command:

    SELECT * INTO trace_table1 FROM ::fn_trace_gettable('C:\PWB_Test\T.trc', 1)

    The job went fine....and also deleted my T.trc file from folder...

    But how do I do for the rest of the files now...

    If I do change the value to 2 or 3 it says that, 'trace_table1' already exists... 🙁 job fails..

    Any suggestions for this one?

    Cheers

    What HanShi is saying is that your "SELECT INTO ...." statement creates the table and loads it in 1 step. But for file 2, since the table now exists, you need to use "INSERT INTO ..." which will only load the table, not try to create it.

    And that you need some logic to sequentially process all the files.

  • June 24, 2013 at 3:07 pm

    #1626931

    I recall plinking around with server-side traces a while back and vaguely remember that if Profiler is writing to the file, it locks the file. I had to write a job to stop/start, forcing a new file and releasing the old one for importing into my table. My requirements needed the trace to start before prime time and end after primetime rather than 7/24 so I had 3 jobs: 1 to start in the morning, 1 to stop/start throughout the day at intervals and 1 to stop for the night.

Viewing 7 posts - 16 through 21 (of 21 total)

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