SQL Server provides a Default Trace of 34 selected events that can be accessed via tools like SQL Profiler or directly via T-SQL. The SQL Server Default Trace is enabled by default. It’s important to note that when a trace is set up to collect only a few events, it runs in the background and has little impact on the database server’s overall performance. However, collecting a large number of events and viewing traces in real-time via the GUI may degrade server performance, so when you need to run a trace via SQL Profiler, keep this in mind and capture only the events that are required. To get all traces running on your Instance, use the below query.
Get Trace file.
SELECT * FROM sys.traces
To reading the trace
file we will use fn_trace_geteventinfo function.
|
SELECT DISTINCT Trace.eventid,
TraceEvents.NAME AS Event_Desc FROM ::fn_trace_geteventinfo(1) Trace, sys.trace_events TraceEvents WHERE Trace.eventid = TraceEvents.trace_event_id |
When we are creating a table or dropping the table it is logged into the trace file.
Create a table
|
create table emp ( emp_name varchar(50), emp_add varchar(100), mob varchar(10) ) |
Table created
successfully.
|
DECLARE @current_tracefilename VARCHAR(500); DECLARE @0_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @current_tracefilename = path FROM sys.traces WHERE is_default = 1; SET @current_tracefilename = Reverse(@current_tracefilename); SELECT @indx = Patindex('%\%', @current_tracefilename); SET @current_tracefilename = Reverse(@current_tracefilename); SET @0_tracefilename = LEFT(@current_tracefilename, Len(@current_tracefilename) - @indx) + '\log.trc'; SELECT databasename, objectname, te.NAME, filename, CONVERT(DECIMAL(10, 3), duration / 1000000e0) AS TimeTakenSeconds, starttime, endtime, ( integerdata * 8.0 / 1024 )
AS 'ChangeInSize M', applicationname, hostname, loginname FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t INNER JOIN sys.trace_events AS te ON t.eventclass = te.trace_event_id WHERE databasename
= 'test_trace' AND objectname = 'emp' ORDER BY 1; |
See the result.
Now dropping this
table.
See the trace now.
We can fetch a lot of useful data using default trace.
