A couple of weeks ago I wrote a post (How to Change Database Settings with the PDO_SQLSRV Driver) that highlighted a problem that came up at the November JumpIn! Camp in Redmond. To get to the root of the problem (and eventually a solution), I used SQL Profiler to monitor events on my database server. Of course, monitoring server events can be helpful in troubleshooting any issue, including performance issues, when it comes to data-driven applications. Unfortunately, SQL Profiler (a tool that allows you to easily monitor server activity) is not included as part of SQL Server Express. Fortunately, SQL Profiler is just a nice UI for functionality that is built into SQL Server (including the Express version). So, in this post I’ll show you how to create, read from, and write to a trace file using SQL and SQL Server Management Studio (which you can download for free from here).
Note: While SQL Profiler is not included with SQL Server Express, it is included in the Developer version, which sells for about $50 (and I’ve seen slightly better prices on Amazon).
1. Create a trace. Open SQL Server Management Studio and execute the Transact-SQL below. Note that the sp_trace_create stored procedure is used to create a trace (see the sp_trace_create documentation for a detailed description of the stored procedure and its parameters). The code below will create a trace file (Trace.trc) in the C:\Users\Public\Documents folder. The .trc extension will be appended automatically to the trace file name. The file should not already exist.
/* Declare variables used in trace creation */
declare @rc int -- Return Code for sp_trace_create
declare @TraceID int -- Trace ID created by sp_trace_create
declare @maxfilesize bigint -- Max size in MB for trace file
set @maxfilesize = 5
/* Create the trace. */
exec @rc = sp_trace_create @TraceID output,
0,
N'C:\Users\Public\Documents\Trace',
@maxfilesize,
NULL
2. Set the events to be monitored. The hardest part in setting up a trace is in deciding what information you want to collect. The sp_trace_setevent stored procedure is used to set the events to be monitored (see the sp_trace_setevent documentation for more info). If you follow that link to the documentation you’ll see that there is a long list of events that you can collect information about. And, for each event, you have to decide what information you want to collect (see the @columnid parameter in the documentation). To keep things relatively simple, the Transact-SQL below collects some information for the RPC:Starting, RPC:Completed, SQL:BatchStarting,
Read more: Brian Swan
Note: While SQL Profiler is not included with SQL Server Express, it is included in the Developer version, which sells for about $50 (and I’ve seen slightly better prices on Amazon).
1. Create a trace. Open SQL Server Management Studio and execute the Transact-SQL below. Note that the sp_trace_create stored procedure is used to create a trace (see the sp_trace_create documentation for a detailed description of the stored procedure and its parameters). The code below will create a trace file (Trace.trc) in the C:\Users\Public\Documents folder. The .trc extension will be appended automatically to the trace file name. The file should not already exist.
/* Declare variables used in trace creation */
declare @rc int -- Return Code for sp_trace_create
declare @TraceID int -- Trace ID created by sp_trace_create
declare @maxfilesize bigint -- Max size in MB for trace file
set @maxfilesize = 5
/* Create the trace. */
exec @rc = sp_trace_create @TraceID output,
0,
N'C:\Users\Public\Documents\Trace',
@maxfilesize,
NULL
2. Set the events to be monitored. The hardest part in setting up a trace is in deciding what information you want to collect. The sp_trace_setevent stored procedure is used to set the events to be monitored (see the sp_trace_setevent documentation for more info). If you follow that link to the documentation you’ll see that there is a long list of events that you can collect information about. And, for each event, you have to decide what information you want to collect (see the @columnid parameter in the documentation). To keep things relatively simple, the Transact-SQL below collects some information for the RPC:Starting, RPC:Completed, SQL:BatchStarting,
Read more: Brian Swan