SQL Profiler Command Line Options

I find it very helpful that when I am developing applications that use a database to see what is being sent to the database. Now, let’s be clear – I think anyone that does any development against a SQL Server database should be aware of SQL Profiler and can use its basic functionality. So, to help speed some of that up (devs are always looking for shortcuts) here are the command line options, one of my samples and descriptions of the options.

clip_image001

Very simply put, you can begin a trace with trusted credentials and SQL Profiler will use the default trace template. So, other than the default command line you can simply use

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\PROFILER90.EXE" /E

This uses the local SQL Server instance, profiling all of the databases my local Windows account has access to (all of them). But for me that isn’t quite enough. The default trace template has too much data in it (just for cursory overviews of sql statements coming into the database) so I have another trace template setup for basic tracing. So, that changes my command line to this

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\PROFILER90.EXE" /S. /E /T"Basic Trace"

I specify the server (my local machine) and trusted connection for completeness. As for the trace templates, I’ll fully explain how to build trace templates in another blog post. Of course, it will be geared towards a developer (from a client perspective – like .Net).

For a reasonable explanation of the options, I’ll paraphrase from SQL BOL (SQL Server Books Online)

/?

Displays the syntax summary of profiler90 arguments.

/U login_id

/P password

/E

Specifies connecting with Windows Authentication with the current user's credentials.

/S sql_server_name

Specifies an instance of SQL Server. Profiler will automatically connect to the specified server using the authentication information specified in the /U and /P switches or the /E switch. To connect to a named instance of SQL Server, use /S sql_server_name\instance_name. In the absence of /U /P and /E switches, /E is assumed

/A analysis_services_server_name

Specifies an instance of Analysis Services. Profiler will automatically connect as specified above.

/D database

Specifies the name of the database. This will not filter the trace if your user context has access to more than the default database

/B "trace_table_name"

Specifies a trace table to load when the profiler is launched. You must specify the database, the user or schema, and the table in a three-part name. Preference here is to use quoted identifiers.

/T "template_name"

Specifies the template that will be loaded to configure the trace.

/F "filename"

Specifies the path and filename of a trace file to load when profiler is launched. The entire path and filename must be in quotes. This option cannot be used with /O.

/O "filename"

Specifies the path and filename of a file to which trace results should be written. The entire path and filename must be in quotes. This option cannot be used with /F.

/L locale_ID

Not available.

/M "MM-DD-YY hh:mm:ss"

Specifies the date and time for the trace to stop. The stop time must be in quotes. Each date parameter is a two-digit part

/R

Enables trace file rollover.

/Z file_size

Specifies the size of the trace file in megabytes (MB). The default size is 5 MB. If rollover is enabled, all rollover files will be limited to the value specified in this argument.

These days, I am not typically running long standing traces against SQL Servers. When I need to do that, I approach it from the standpoint of building a sql script to create a server side trace instead of using the client tool (that is much more performant). Here is an example of scripting a SQL Profiler trace.

The bottom line here is that is can be very easy to simplify tracing and make it easy to see what is going on in the background of your application. Of course, never forget to stop your trace when you are done. The trace will stay active on your server (until reboot or you manually stop it) if you just disconnect.

2 Comments

  1. Jorge Says:

    Stop time parameter must be in mssql date format. p.e. "2012-11-19 18:00:00:0000". Others formats not working, and set stop time 1 hour after start time.

  2. Andre Says:

    Tracing the Analysis Services AND writing to a relational table does not work for me (the call works, if I leave out the option /B, so the AS connect is not the problem):

    "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\PROFILER.EXE" /E /A myASServer\myASInstance /D myASDatabase /B "myServer.myDatabase.mySchema.myTablename"

    Profiler starts (without displaying a tracing window) and does not respond. A trace output table cannot be found.


Leave a Reply