Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Published: Sep 18, 2015
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
@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')
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
@textData = '%'+ @textData + '%'
exec sp_trace_setfilter @traceID, @columnID = 1, @logical_operator = 0, @comparison_operator = 6, @value = @textData

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
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)
@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

from #traceDataRaw
order by startTime, spid
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.