The script:
declare @traceid int;
declare @ret int;
declare @maxfilesize bigint;
declare @on bit;
set @maxfilesize = 10;
set @onbit = 1;
--select * from sys.traces;
/* this statement list all the current traces in the system
Status 0 => stopped
Status 1 => Runnning
*/
exec @ret = sp_trace_create @traceid output,
0,
N'D:\MyPath\MyTraceFile.trc', -- change this name accordingly
@maxfilesize,
Null
/*Print @ret*/
--select * from sys.traces;
/* above statement should list your new trace with status = 0.
*/
/*Now we have successfully created trace, lets add events and columns to be traced*/
/*==============RPC completed====================*/
exec sp_trace_setEvent @traceid,
10 ,
1,
@onbit;
exec sp_trace_setEvent @traceid,
10 ,
2,
@onbit;
exec sp_trace_setEvent @traceid,
10 ,
8,
@onbit;
exec sp_trace_setEvent @traceid,
10 ,
10,
@onbit;
exec sp_trace_setEvent @traceid,
10 ,
35,
@onbit;
exec sp_trace_setEvent @traceid,
10 ,
14,
@onbit;
exec sp_trace_setEvent @traceid,
10 ,
15,
@onbit;
/*==============End RPC completed==================== */
/*==============SQL batch completed==================== */
exec sp_trace_setEvent @traceid,
12 ,
1,
@onbit;
exec sp_trace_setEvent @traceid,
12 ,
2,
@onbit;
exec sp_trace_setEvent @traceid,
12 ,
8,
@onbit;
exec sp_trace_setEvent @traceid,
12 ,
10,
@onbit;
exec sp_trace_setEvent @traceid,
12 ,
35,
@onbit;
exec sp_trace_setEvent @traceid,
12 ,
14,
@onbit;
exec sp_trace_setEvent @traceid,
12 ,
15,
@onbit;
/*==============End SQL batch completed==================== */
/*==============SQL Statement completed==================== */
exec sp_trace_setEvent @traceid,
41 ,
1,
@onbit;
exec sp_trace_setEvent @traceid,
41 ,
2,
@onbit;
exec sp_trace_setEvent @traceid,
41 ,
8,
@onbit;
exec sp_trace_setEvent @traceid,
41 ,
10,
@onbit;
exec sp_trace_setEvent @traceid,
41 ,
35,
@onbit;
exec sp_trace_setEvent @traceid,
41 ,
14,
@onbit;
exec sp_trace_setEvent @traceid,
41 ,
15,
@onbit;
/*==============End SQL Statement completed==================== */
exec sp_trace_setfilter @traceid,
35, -- database name
0, -- logical operator 0=> AND
6, -- comparison 6 => like
N'MYDBName';
exec sp_trace_setStatus @traceid,1;
-- 1 => start 0 => stop 2=> close
go
select * from sys.traces;
|
Above query initializes and activates the trace on SQL server, which writes to the specified file location. The file location has to be on a local drive and the SQL server service account should have full privilege on the specified location.
The output trace when opened with SQL Profiler looks as:
System catalog view sys.traces lists all the current traces and their status.
So the following statement should now list 2 traces with status as 1, one of the trace being the default trace with id = 1, and the second one which we created above.
select * from sys.traces; |
Script to disable the trace
declare @traceid int;
select * from sys.traces;
-- get the trace ID you want to disable
set @traceid = 2 ;
exec sp_trace_setStatus @traceid,
0; -- 1 => start 0 => stop 2=> close
go
select * from sys.traces;
-- here we have the trace in stoped mode
exec sp_trace_setStatus @traceid,
2; -- 1 => start 0 => stop 2=> close
--Now we have the trace closed and removed from system
select * from sys.traces;
-- select willnot list our trace.
|
Again executing a select on sys.traces should result in single row, indicating only the default trace is present on the server
select * from sys.traces; |
Enjoy!!
Deciphering our main query
Our main query starts with the statement defining certain properties of our trace
exec @ret = sp_trace_create @traceid output,
0,
N'D:\MyPath\MyTraceFile.trc',
@maxfilesize,
Null
|
XSP sp_trace_create takes 6 parameters, last 3 being optional, the parameters are as follows:
Param
|
Values
|
Description
| |
1
|
TraceID
|
Int, Output
|
Output value containing the generated trace ID
|
2
|
Options
|
0
|
Default
|
2
|
“Trace file rollover”, specifying the tracing engine to generate a new file when Max file limit is reached.
| ||
4
|
“Shutdown on error” SQL shuts down if traces cannot be written to the file
| ||
8
|
“Trace Produce Blackbox” this is incompaitable with all the other trace levels and max file size cannot be specified along with this.
| ||
3
|
maxFileSize
|
Bigint
|
Specifies maximum trace file in MB
|
4
|
StopTime
|
Datetime
|
Date time at which this trace will stop.
|
5
|
FileCount
|
Int
|
Maximum number of trace files to be maintained (older trace files are deleted)
|
For further information please refer to msdn : http://msdn.microsoft.com/en-us/library/ms190362.aspx
The second step is to select events and columns for tracing.
In SQL server 2008 r2 as well as in 2012, there are 180 events divided into 21 categories
The following queries on trace catalog views list the events, categories and join between them
select * from sys.trace_categories;
select * from sys.trace_events;
select
c.name as 'Category',
e.name as 'TraceEvent',
e.trace_event_id as 'EventID'
from
sys.trace_events e
join sys.trace_categories c
on e.category_id = c.category_id
Order by 1,2
|
Event columns are described by sys.trace_columns catalog view and the association of trace columns to trace events is defined by sys.trace_event_bindings. There are 66 trace event columns. Following query gets the event column definition for all the events. This query on SQL 2008 R2 should return 4304 rows.
select
ev.trace_event_id as 'EventID',
ev.name as 'Event',
cl.trace_column_id as 'ColID',
cl.name as 'ColName',
cl.type_name as 'ColType'
from
sys.trace_event_bindings bi
join sys.trace_events ev on bi.trace_event_id = ev.trace_event_id
join sys.trace_columns cl on bi.trace_column_id = cl.trace_column_id
|
Based on output from the catalog query above, provide inputs to sp_trace_setEvent XSP
XSP sp_trace_setEvent takes the following inputs
Param
|
Values
|
Description
| |
1
|
TraceID
|
Int
|
ID of trace tobe modified
|
2
|
EventID
|
Int
|
ID of event
|
3
|
ColumnID
|
Int
|
ID of column to be traced
|
4
|
OnBit
|
Bit
|
0 => off, 1 => on (delete & add as well).
|
Setting filter to trace
We would certainly do not want to log events happening across all the databases on the server. Definitely we would like to filter, XSP sp_trace_setfilter does the filtering for us.
XSP sp_trace_setfilter takes the following parameters
Param
|
Values
|
Description
| |
1
|
TraceID
|
Int
|
ID of trace tobe modified
|
2
|
ColumnID
|
Int
|
The column id to be compared (35 indicates database name, from sys.trace_columns catalog view
|
3
|
Logicaloperator
|
0 / 1
|
Used in conjunction with other filters 0 => AND 1 => OR operation
|
4
|
Comparison
|
0 – 7
|
0. =
1. <>
2. >
3. <
4. >=
5. <=
6. Like
7. Not Like
|
5
|
Value
|
NVarchar
|
For detailed reference, please refer msdn: http://msdn.microsoft.com/en-us/library/ms174404.aspx
Running the trace
By default when we first create the trace, its status is “0”, indicating its created but in stop mode. To set the trace to start mode, use the following query.
exec sp_trace_setStatus @traceid,
1; -- 1 => start 0 => stop 2=> close
|
Now you should have the trace up and running.
Caution
Remember trace generates and writes data on to local file system. For a server already facing IO crunch, this might significantly add up to the resource crunch. In case of C: drive, this might fill up the OS drive.
To view the trace file, you can either open the trace file with SQL Server Profiler (run as administrator) but will not be able to refresh the new entries.
Other way is to use the following SQL query for viewing the trace file. This returns all the available 66 columns.
select
*
from
fn_trace_gettable( N'C:\Path\DBTrace.trc',0);
|
No comments:
Post a Comment