Run MS SQL Profiler via command line

• JL-948550

  SSC Veteran

  Points: 201

  More actions

  November 23, 2008 at 8:45 pm

  #215668

  Hi all,

  As I am new to MS SQL, I am not very familiar with it and hope that someone out there can help me

  Currently I am running Profiler90 via profiler GUI and outputting the filtered results into another table of another database. However, it is so inconvenient that I need to re-create the trace of the profiler whenever the database is reset or rebooted for maintenance.

  I have done some research that profiler can be started up via command line but only output to a file and not a table.

  Has someone got any idea on how to create the profiler via command line with outputting the results to the table?

  Your help is greatly appreciated.:)

  JL

• Elisabeth Rédei

  Hall of Fame

  Points: 3873

  More actions

  November 24, 2008 at 11:38 am

  #902662

  Hi,

  You are right, there is now way to start the trace from script and direct the output to a table. However, you can direct the output to a trace file and then load the trace file created into a table by using the fn_trace_gettable system function.

  So export the tracedefinition you have defined in Profiler (File/Export) and then run the script to create the trace on the server (first change filename etc.). Use sp_trace_setstatus to start the trace.

  If you want to have this trace to start automatically when the server starts/after reboot, create a procedure that runs sp_trace_setstatus and then run sp_procoption to make that new procedure a startup procedure.

  Oh, btw, you can create a Profiler template to avoid having to redefine your traces in Profiler.

  HTH

  /Elisabeth

  elisabeth@sqlserverland.com
  MCITP | MCT
  http://sqlblog.com/blogs/elisabeth_redei/
  http://linkedin.com/in/elisabethredei