I have been using SQL Server Profiler for years to run traces against SQL Server, it is a indispensable resource for when you need to find out what is being ran against your database, or when you really need to dive into what is going on. If you are not familar with SQL Server Profiler then check out this article on TechRepublic and then come back here.

What I have not used is server side tracing. SQL Server Profiler is just a GUI built on top of the tracing functionality that is built into the SQL Server engine. It turns out that using tracing functionality in SQL Server can be very powerful, and SQL Server Profiler is actually a great script generator for building your own traces. Let me give a example to clear things up.  I would run this example on a test database.  When running a trace on a busy production server I would apply more filters, and only trace the events that you need in order to avoid causing any performance issues.

Launch SQL Server Profiler and start a trace against your database, you can just use the Standard template that is built into SQL Server Profiler. Start the trace and then stop it (you have to have started the trace in order to be able to generate a script). Then go to the menu and select File -> Export -> Script Trace Definition -> For SQL Server 2005 ..2008. This will save a sql file. Open this file up in SQL Server Management Studio and you will see something like this:
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 06/17/2010 08:39:09 PM */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- 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, N'InsertFileNameHere', @maxfilesize, NULL
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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @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, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @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, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 73dd2758-d97c-405b-ae7e-3f67bee289ba'
-- 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

The first section of this code is setting up @TraceId and @maxfilesize variables. The @TraceId will be used to get the id of the trace back from the stored procedure that creates the trace. The @maxfilesize variable configures how big the file is allowed to get, once the file reaches this size the trace stops.

The next section of code creates the trace by calling the sp_trace_create stored procedure. This creates the trace but does not start it. The first parameter passed to this stored procedure is the @TraceId, this is an output parameter and this parameter will be populated with the Id of the trace that is created. The second parameter specifies whether to use rollover files, when this parameter is set to 0 then rollover files will not be used, and when the file reaches the max file size the trace stops. If this parameter is set to 1 then when the file size reaches the max size, then it rolls over to a new file. The third parameter specifies what file to output the trace to, when SQL Server Profiler generates the script it just puts a generic value in here, you need to replace it with the path and file name that you want the trace data saved to. The forth parameter specifies the max file size.

Next the script uses a bunch of sp_trace_setevent statements to configure what events should be traced, this is the part that is really nice to have generated for you.

Next the script uses the sp_trace_setfilter stored procedure to set the filters for the trace.

Finally it uses the sp_trace_setstatus stored procedure to set the status of the trace to 1, which starts the trace.

Once the trace has started collecting data you can used the fn_trace_gettable function to query the data in the file. This function takes two parameters, the first parameter is the file to load, and the second parameter is how many rollover files to load. If you are not using rollover files then you can just pass in a value of 1 for the second parameter. The beauty of this is that you can easily us a SELECT INTO statement to load the file into a table, and then run whatever queries you want against it. For example to load my trace file into a table I used the following query:
SELECT *
INTO MYTraceTable
FROM fn_trace_gettable(N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testrace.trc', 1);

This loads the trace data from the file into the MyTraceTable table. You an not run whatever queries you want against this data.

Personally I have found that when I am debugging a application, and want to see what SQL code is being executed against a database it is easier to just use SQL Server Profiler. SQL Server Profiler makes it very easy to start and stop a trace, and it displays the SQL code in a format that is fairly easy to read. When I am run a trace that I want to being running unattended, or if I am looking at performance data, then I find server side traces to be more useful.