Using the SQL Server Default Trace to Audit Events

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.