QuickTrace
Published: 6th April 2014
This was something I created due to being fed up with constantly having to set up Profiler, having it “forget” my custom traces, and also wanting the data in SSMS so that I could query it if necessary.

The result of this was something I simply named “QuickTrace” because it’s a quick way to run an effective and consistent trace. That’s it really.

As a disclaimer, this is NOT guaranteed to work on all machines due to permissions issues, but it worked for me and you might be able to adapt it to work for you.

Also, functionality I use here being trace rather than Extended Events means that this is technically listed as deprecated… but it’ll work for a good while yet on all existing and previous SQL Server versions.

Sample output from the proc is as follows (based on a 30 second trace looking for anything containing "salesOrder"):

exec quickTrace @textData='salesOrder', @traceTime=30


And the proc itself is defined like this:

exec dbo.quickTrace
      
@databaseName nvarchar(250) = null,
          
-- optional parameter to trace a specific database
      
@textData nvarchar(250) = null,
          
-- optional parameter to filter on specific text.  % not necessary
      
@duration bigint = null,
          
-- optional parameter to filter by duration in ms
      
@traceTime int = 5
          
-- how long you want the trace to run


Effectively the code looks for the folder in which you have your data files and uses that in order to set up a trace, write to file, reads the file contents to SSMS, then deletes the file.

This is a very small trace file and when restricted through filters is very light on the disks, but if you don’t want to risk impacting your disks then feel free to change the code so that you hard code a location. In my case the disks were VERY under-utilised and therefore this wasn’t an issue and meant that I could run the same code across a couple of servers easily.

It also relies on xp_cmdShell. Therefore I’d recommend this only for a test box really. Still useful though.

The code I used was as follows:

create procedure dbo.quickTrace
  
@databaseName nvarchar(250) = null,
  
@textData nvarchar(250) = null,
  
@duration bigint = null,
  
@traceTime int = 5
as
   set
@duration = @duration * 1000

  
declare @traceFolder nvarchar(255), @stop datetime = dateadd(ss, @traceTime, current_timestamp)

  
select @traceFolder =
  
(
      
select top 1 physical_name
      
from sys.databases d
      
join sys.master_files m
      
on d.database_id = m.database_id
      
where d.name not in ('master', 'msdb', 'model', 'tempDB')
       and
type_desc != 'LOG'
  
)

  
select @traceFolder = left(@traceFolder, len(@traceFolder)-charindex('\', reverse(@traceFolder), 1))
  
declare @actualFile varchar(255) = 'quickTrace' + convert(varchar(10), floor((convert(int, convert(varbinary(3), newID())))+1))

  
declare @traceID int, @maxFileSize bigint = 10,
          
@traceFile nvarchar(255) = @traceFolder + '\' + @actualFile

  
exec sp_trace_create @traceID output,
      
@options = 6,
      
@traceFile = @traceFile,
      
@maxFileSize = @maxFileSize,
      
@stopTime = @stop

  
exec sp_trace_setevent @TraceID, 12, 1, 1
  
exec sp_trace_setevent @TraceID, 12, 3, 1
  
exec sp_trace_setevent @TraceID, 12, 11, 1
  
exec sp_trace_setevent @TraceID, 12, 12, 1
  
exec sp_trace_setevent @TraceID, 12, 13, 1
  
exec sp_trace_setevent @TraceID, 12, 14, 1
  
exec sp_trace_setevent @TraceID, 12, 15, 1
  
exec sp_trace_setevent @TraceID, 12, 16, 1
  
exec sp_trace_setevent @TraceID, 12, 17, 1
  
exec sp_trace_setevent @TraceID, 12, 18, 1
  
exec sp_trace_setevent @TraceID, 12, 35, 1

  
if @databaseName is not null
      
exec sp_trace_setfilter @traceID, @columnID = 35, @logical_operator = 0, @comparison_operator = 4, @value = @databaseName

  
if @duration is not null
      
exec sp_trace_setfilter @traceID, @columnID = 13, @logical_operator = 0, @comparison_operator = 4, @value = @duration

  
if @textData is not null
  
begin
       select
@textData = '%'+ @textData + '%'
      
exec sp_trace_setfilter @traceID, @columnID = 1, @logical_operator = 0, @comparison_operator = 6, @value = @textData
  
end

   exec
sp_trace_setstatus @traceID, 1

  
print 'Trace running...'

  
while current_timestamp < @stop
      
waitfor delay '00:00:01'

  
declare @dir varchar(250)
  
select @dir = 'dir "' + @traceFolder + '"'

  
declare @files table
  
(
      
data varchar(2000)
   )
  
insert into @files
  
exec xp_cmdShell @dir

  
delete from @files
  
where data is null
   or
data not like '%.trc%'
  
or data not like '%' + @actualFile + '%'

  
declare @trace table
  
(
      
id int,
      
traceFiles varchar(100)
   )
  
insert into @trace
  
select row_number() over(order by substring(data, charindex('quickTrace', data, 1), len(data))) id,
          
substring(data, charindex('quickTrace', data, 1), len(data)) fileNames
  
from @files

  
declare @counter int = 1, @sql nvarchar(max)

  
create table #traceDataRaw
  
(
      
textData varchar(max),
      
spid int,
      
cpu int,
      
reads int,
      
writes int,
      
duration int,
      
startTime datetime,
      
endTime datetime,
      
databaseName varchar(100)
   )

  
declare @dump table
  
(
      
dumpData varchar(10)
   )

  
while @counter <= (select max(id) from @trace)
  
begin
       select
@sql = '
select textData, SPID, cpu, reads, writes, floor(Duration/1000) duration, StartTime, EndTime, DatabaseName
from ::fn_trace_gettable('''
+ @traceFolder + '\' + traceFiles + ''', default)
where TextData is not null'
,
              
@dir = 'del "' + @traceFolder + '\' + traceFiles + '"'
      
from @trace
      
where id = @counter

      
insert into #traceDataRaw
      
exec sp_executeSQL @sql

      
insert into @dump
      
exec xp_cmdShell @dir

      
select @counter += 1
  
end

   select
*
  
from #traceDataRaw
  
order by startTime, spid
Comments:
Alan
05/05/2015 15:07:00
Excellent stuff, thanks. I have modified the code slightly for my own ends but got me past another problem I was having running a trace through the GUI where SQL was skipping records when the server was under load.

Also I had to change the databasename setfilter line to get it working in the first instance:

if @databaseName is not null
exec sp_trace_setfilter @traceID, @columnID = 35, @logical_operator = 0, @comparison_operator = 0, @value = @databaseName

basically @comparison_operator had to be 0 and not 4.

NB: Comments will only appear once they have been moderated.