Automating the SQL Profiler

• nivedita talukdar

  SSC Veteran

  Points: 272

  More actions

  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

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  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’! **

• nivedita talukdar

  SSC Veteran

  Points: 272

  More actions

  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

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  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’! **

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  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]

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  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.

• Your Name Here

  SSCrazy

  Points: 2344

  More actions

  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.