Mastering SQL Trace Using Profiler - Microsoft SQL Server 2012 Performance Tuning - page 29

The answer is yes. You can do this. SQL Server provides T-SQL system stored procedures
to deal with SQL Trace. This capability enables us to write code that can create traces
programmatically. By using SQL Trace system stored procedures along with SQL Agent, it is
possible to automate and schedule the traces so that they run in background and capture
event data during only certain period of time on a regular basis.
In this recipe, we will see how to create a trace without SQL Server Profiler by using SQL
Trace system stored procedures. The trace that we will create in this recipe can be used
to monitor the file growth of data files and log files of all databases on an instance of SQL
Server. Monitoring file growth event for data files and log files will tell you how frequently your
database files are grown that helps further in determining appropriate values for FILEGROWTH
attribute of database files. If the size of files is increased by a smaller amount (for example, by
1 MB), SQL Server has to increase and extend the size of database files very frequently, which
degrades the performance of write operations while working with large amount of data. It may
also degrade the performance of read operations due to physical file fragmentation caused by
small file chunks that are spread all over on the disk which makes a possible sequential read
a random read. Thus, you should consider setting an appropriate FILEGROWTH value for
your databases.
Getting ready
Before you start with the recipe, it is necessary that you have some background of basic
system stored procedures provided in SQL Server which are used to work with traces.
Following are the stored procedures which you should know:
F sp_trace_create : This stored procedure is used to create a trace and returns the
ID of newly created trace
F sp_trace_setevent : This stored procedure is used to add or remove event classes
and data columns to and from a given trace
F sp_trace_setfilter : This stored procedure is used to set a filter condition on
desired data column for a given trace
F sp_trace_setstatus : This stored procedure is used to start, stop, or close a
given trace
In this example, we will capture only two event classes:
F Data File Auto Grow
F Log File Auto Grow
For these mentioned event classes, we will be capturing the following data columns:
F DatabaseName
F FileName
F StartTime
F EndTime