This is what I used in SQL Server Express 2012 (note: not "LocalDB * - I never used LocalDB, so maybe this is different from regular SQL Server Express).
Step 1: Configure Trace
This is basically "hard work." First you need to find out where the SQL Server default log directory is located. You need this directory name to specify the trace file.
Then create a trace by doing something like this:
DECLARE @TraceID int DECLARE @tracefile nvarchar(255) DECLARE @endDate datetime DECLARE @size bigint -- no file extension! SET @tracefile = 'C:\Data\sqlserver\MSSQL11.SQLEXPRESS\MSSQL\Log\mydb_trace' -- tracing stops when either the max size of the file is reached -- or the enddate (whichever occurs first) -- size is in MB SET @size = 250 SET @enddate = DateAdd(DAY, 15, GetDate()) EXEC @rc = sp_trace_create @TraceID output, 2, @tracefile, @size, @enddate
Now for each event that needs to be traced, you need to call sp_trace_setevent several times to determine which column for this event should be returned:
For a complete list of events and columns, see http://msdn.microsoft.com/en-US/library/ms186265%28v=sql.90%29.aspx
-- Enable Event: 45 = SP:StmtCompleted EXEC sp_trace_setevent @TraceID, 45, 27, @on -- 27: EventClass EXEC sp_trace_setevent @TraceID, 45, 12, @on -- 12: SPID EXEC sp_trace_setevent @TraceID, 45, 35, @on -- 35: DatabaseName EXEC sp_trace_setevent @TraceID, 45, 11, @on -- 11: SQLSecurityLoginName EXEC sp_trace_setevent @TraceID, 45, 6, @on -- 6: NTUserName EXEC sp_trace_setevent @TraceID, 45, 8, @on -- 8: ClientHostName EXEC sp_trace_setevent @TraceID, 45, 10, @on -- 10: ApplicationName EXEC sp_trace_setevent @TraceID, 45, 1, @on -- 1: TextData EXEC sp_trace_setevent @TraceID, 45, 13, @on -- 13: Duration EXEC sp_trace_setevent @TraceID, 45, 14, @on -- 14: StartTime EXEC sp_trace_setevent @TraceID, 45, 15, @on -- 15: EndTime EXEC sp_trace_setevent @TraceID, 45, 18, @on -- 18: CPU EXEC sp_trace_setevent @TraceID, 45, 29, @on -- 29: Nesting Level
All of the above calls must be completed for each event that you want to track!
I find events 12 = SQL:BatchCompleted , 42 = SP:Starting , 43 = SP:Completed , 45 = SP:StmtCompleted , 50 = SQL Transaction most interesting.
If you wish, you can configure the filter, I usually filter out system events and show only events for a specific database:
-- Exclude system events (so only user events are shown) -- 60: IsSystem Column -- 0: logical Operator: AND (only) -- 1: comparison operator: not equal -- 1: value EXEC sp_trace_setfilter @TraceID, 60, 0, 1, 1 -- only mydb database EXEC sp_trace_setfilter @TraceID, 35, 0, 6, N'mydb'
Once the trace is configured, it must be activated:
EXEC sp_trace_setstatus @TraceID, 1
(Note that the above should be run as a separate batch due to the use of the variable).
To find out how the trace was defined, you can use the following statement:
select traceid, case property when 1 then 'Trace Options' when 2 then 'Trace file' when 3 then 'Max. file size' when 4 then 'Stop time' when 5 then 'Status' end as property_name, case when property = 5 then case convert(nvarchar(max), value) when '1' then 'Active' else 'Inactive' end else convert(nvarchar(max), value) end as value from ::fn_trace_getinfo(null) where property in (2,3,5)
Now run the application or any problem operators in the database that you want to track.
Step 2: get trace information
To do this, you need to know the full path to the actual trace file (from step 1). Note that for fn_trace_gettable you need to specify the file, including the file extension.
SELECT ApplicationName, LoginName, HostName, SPID, Duration, StartTime, EndTime, DatabaseName, reads, writes, RowCounts, cpu, EventClass, case EventClass when 10 then 'RPC:Completed' when 11 then 'RPC:Starting' when 12 then 'SQL:BatchCompleted' when 13 then 'SQL:BatchStarting' when 40 then 'SQL:StmtStarting' when 41 then 'SQL:StmtCompleted' when 42 then 'SP:Starting' when 43 then 'SP:Completed' when 44 then 'SP:StmtStarting' when 45 then 'SP:StmtCompleted' when 50 then 'SQL Transaction' when 67 then 'Execution Warnings' when 71 then 'Prepare SQL' when 72 then 'Exec Prepared SQL' when 73 then 'Unprepare SQL' end as Event, LineNumber, TextData FROM ::fn_trace_gettable('C:\Data\sqlserver\MSSQL11.SQLEXPRESS\MSSQL\Log\mydb_trace.log', default) order by StartTime;
Adjust the above to return the information you are interested in.
Once you have the necessary information, you should disable tracing:
Step 3: disable tracing
To do this, you need to know the trace identifier (for example, by running the “information description” from step 1). With this identifier, you need to stop the trace first, then you can delete it:
-- stop the trace EXEC sp_trace_setstatus @TraceID, 0 -- delete the trace EXEC sp_trace_setstatus @TraceID, 2