/****************************************************/
/* Created by: SQL Server 2008 Profiler   */
/* Date: 12/08/2009 01:35:38 PM   */
/****************************************************/

-- Create a Queue
declare @rc int , @TraceID int
declare @maxfilesize bigint, @filecount int, @stoptime datetime, @tracefile nvarchar (200)
set @maxfilesize = 5 SET @filecount = 1000
SELECT @stoptime = DATEADD(dd, 1, CAST( GETDATE() AS date) )
SELECT @tracefile = N'C:\Trace' + CONVERT(nvarchar, GETDATE(),112) + '_'
SELECT @stoptime, @tracefile

/*sp_trace_create [ @traceid = ] trace_id OUTPUT
       , [ @options = ] option_value
       , [ @tracefile = ] 'trace_file'
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]*/

exec @rc = sp_trace_create @TraceID output, 2, @tracefile, @maxfilesize, @stoptime , @filecount

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1

-- RPC:Completed
-- exec sp_trace_setevent @TraceID, 27 -- EventClass, should this be here?
exec sp_trace_setevent @TraceID, 10, 2, @on -- Binary Data
exec sp_trace_setevent @TraceID, 10, 3, @on -- DatabaseID
exec sp_trace_setevent @TraceID, 10, 9, @on -- ClientProcessID
exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @on -- StartTime
exec sp_trace_setevent @TraceID, 10, 15, @on -- EndTime
exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 10, 25, @on -- IntegerData
exec sp_trace_setevent @TraceID, 10, 48, @on -- RowCounts

-- RPC:StmtCompleted?
exec sp_trace_setevent @TraceID, 45, 1, @on -- TextData
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 29, @on -- NestLevel
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 55, @on -- IntegerData2
--exec sp_trace_setevent @TraceID, 45, 61, @on   --Offset

-- SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 48, @on

-- degree of parallelism
-- Probably not necessary, used for special circumstances
--exec sp_trace_setevent @TraceID, 28, 2, @on
--exec sp_trace_setevent @TraceID, 28, 9, @on
--exec sp_trace_setevent @TraceID, 28, 10, @on -- Application Name
--exec sp_trace_setevent @TraceID, 28, 12, @on
--exec sp_trace_setevent @TraceID, 28, 14, @on

 

-- Showplan XML Statistics Profile
-- Generally not recommended, used for special circumstances
--exec sp_trace_setevent @TraceID, 146, 1, @on
--exec sp_trace_setevent @TraceID, 146, 2, @on
--exec sp_trace_setevent @TraceID, 146, 9, @on
--exec sp_trace_setevent @TraceID, 146, 10, @on
--exec sp_trace_setevent @TraceID, 146, 12, @on
--exec sp_trace_setevent @TraceID, 146, 14, @on

-- Showplan XML For Query Compile
-- Be careful with this, especially if plan compiles are high
exec sp_trace_setevent @TraceID, 168, 1, @on
exec sp_trace_setevent @TraceID, 168, 2, @on
exec sp_trace_setevent @TraceID, 168, 9, @on
--exec sp_trace_setevent @TraceID, 168, 10, @on
exec sp_trace_setevent @TraceID, 168, 12, @on
exec sp_trace_setevent @TraceID, 168, 14, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 5
-- sp_trace_setfilter @traceid, @columnid, @logical_operator, @comparison_operator, @value
-- @logical_operator: 0 AND, @comparison_operator: 4 >= (Greater Than Or Equal)

exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

 

error:
select ErrorCode=@rc

 

finish:
go

 

--exec sp_trace_setstatus 3, 0
--exec sp_trace_setstatus 3, 2
--SELECT * FROM ::fn_trace_getinfo(0)

/*

TextData          1
Binary Data       2
Database ID       3
Transaction ID    4
LineNumber        5
NTUserName        6
NTDomainName      7
HostName          8
ClientProcessID 9
ApplicationName 10
LoginName         11
SPID              12
Duration          13
StartTime         14
EndTime           15
Reads             16
Writes            17
CPU               18
Permissions       19
Severity          20
EventSubClass     21
ObjectID          22
Success           23
IndexID           24
IntegerData       25
ServerName 26
EventClass 1      27
ObjectType 2      28
NestLevel   29
State 30
Error 31
Mode  32
Handle      33
ObjectName  34
DatabaseName      35
FileName    36
OwnerName   37
RoleName    38
TargetUserName    39
DBUserName 1      40
LoginSid 1  41
TargetLoginName   42
TargetLoginSid    43
ColumnPermissions 44
LinkedServerName  45
ProviderName      46
MethodName  47
RowCounts   48
RequestID   49
XactSequence      50
EventSequence     51
BigintData1 52
BigintData2 53
GUID  54
IntegerData2      55
ObjectID2   56
Type  57
OwnerID     58
ParentName  59
IsSystem    60
Offset      61
SourceDatabaseID  62
SqlHandle   63
SessionLoginName  64
*/

 

/*

0-9   Reserved
10    RPC:Completed
11    RPC:Starting
12    SQL:BatchCompleted
13    SQL:BatchStarting
14    Audit Login
15    Audit Logout
16    Attention
17    ExistingConnection
18    Audit Server Starts and Stops
19    DTCTransaction
20    Audit Login Failed
21    EventLog
22    ErrorLog
23    Lock:Released
24    Lock:Acquired
25    Lock:Deadlock
26    Lock:Cancel
27    Lock:Timeout
28    Degree of Parallelism Event (7.0 Insert)
29-31 Reserved
32    Reserved
33    Exception
34    SP:CacheMiss
35    SP:CacheInsert
36    SP:CacheRemove
37    SP:Recompile
38    SP:CacheHit
39    Deprecated
40    SQL:StmtStarting
41    SQL:StmtCompleted
42    SP:Starting
43    SP:Completed
44    SP:StmtStarting
45    SP:StmtCompleted
46    Object:Created
47    Object:Deleted
48    Reserved
49    Reserved
50    SQL Transaction
51    Scan:Started
52    Scan:Stopped
53    CursorOpen
54    TransactionLog
55    Hash Warning
56-57 Reserved
58    Auto Stats
59    Lock:Deadlock Chain
60    Lock:Escalation
61    OLE DB Errors
62-66 Reserved
67    Execution Warnings
68    Showplan Text (Unencoded)
69    Sort Warnings
70    CursorPrepare
71    Prepare SQL
72    Exec Prepared SQL
73    Unprepare SQL
74    CursorExecute
75    CursorRecompile
76    CursorImplicitConversion
77    CursorUnprepare
78    CursorClose
79    Missing Column Statistics
80    Missing Join Predicate
81    Server Memory Change
82-91 User Configurable (0-9)
92    Data File Auto Grow
93    Log File Auto Grow
94    Data File Auto Shrink
95    Log File Auto Shrink
96    Showplan Text
97    Showplan All
98    Showplan Statistics Profile
99    Reserved
100   RPC Output Parameter
101   Reserved
102   Audit Statement GDR Event
103   Audit Object GDR Event
104   Audit AddLogin Event
105   Audit Login GDR Event
106   Audit Login Change Property Event
107   Audit Login Change Password Event
108   Audit Add Login to Server Role Event
109   Audit Add DB User Event
110   Audit Add Member to DB Role Event
111   Audit Add Role Event
112   Audit App Role Change Password Event
113   Audit Statement Permission Event
114   Audit Schema Object Access Event
115   Audit Backup/Restore Event
116   Audit DBCC Event
117   Audit Change Audit Event
118   Audit Object Derived Permission Event
119   OLEDB Call Event
120   OLEDB QueryInterface Event
121   OLEDB DataRead Event
122   Showplan XML
123   SQL:FullTextQuery
124   Broker:Conversation
125   Deprecation Announcement
126   Deprecation Final Support
127   Exchange Spill Event
128   Audit Database Management Event
129   Audit Database Object Management Event
130   Audit Database Principal Management Event
131   Audit Schema Object Management Event
132   Audit Server Principal Impersonation Event
133   Audit Database Principal Impersonation Event
134   Audit Server Object Take Ownership Event
135   Audit Database Object Take Ownership Event
136   Broker:Conversation Group
137   Blocked Process Report
138   Broker:Connection
139   Broker:Forwarded Message Sent
140   Broker:Forwarded Message Dropped
141   Broker:Message Classify
142   Broker:Transmission
143   Broker:Queue Disabled
144-145     Reserved
146   Showplan XML Statistics Profile
148   Deadlock Graph
149   Broker:Remote Message Acknowledgement
150   Trace File Close
151   Reserved
152   Audit Change Database Owner
153   Audit Schema Object Take Ownership Event
154   Reserved
155   FT:Crawl Started
156   FT:Crawl Stopped
157   FT:Crawl Aborted
158   Audit Broker Conversation
159   Audit Broker Login
160   Broker:Message Undeliverable
161   Broker:Corrupted Message
162   User Error Message
163   Broker:Activation
164   Object:Altered
165   Performance statistics
166   SQL:StmtRecompile
167   Database Mirroring State Change
168   Showplan XML For Query Compile
169   Showplan All For Query Compile
170   Audit Server Scope GDR Event
171   Audit Server Object GDR Event
172   Audit Database Object GDR Event
173   Audit Server Operation Event
175   Audit Server Alter Trace Event
176   Audit Server Object Management Event
177   Audit Server Principal Management Event
178   Audit Database Operation Event
180   Audit Database Object Access Event
181   TM: Begin Tran starting
182   TM: Begin Tran completed
183   TM: Promote Tran starting
184   TM: Promote Tran completed
185   TM: Commit Tran starting
186   TM: Commit Tran completed
187   TM: Rollback Tran starting
188   TM: Rollback Tran completed
189   Lock:Timeout (timeout > 0)
190   Progress Report: Online Index Operation
191   TM: Save Tran starting
192   TM: Save Tran completed
193   Background Job Error
194   OLEDB Provider Information
195   Mount Tape<
196   Assembly Load
197   Reserved
198   XQuery Static Type
199   QN: subscription
*/