CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
Rohit Kesharwani
Rohit Kesharwani

Reputation: 83

How to trace SQL Server Events data in database table?

How to trace SQL Server Events data in database table?

Here I created a trace event for login and logout in sql server, which log and trace the data in a given file path.

But I want to store event data in custom sql server database table instead of file.

declare @tracefile nvarchar(500) set @tracefile=N'd:\rohit\newtraceFile'
declare @trace_id int
declare @maxsize bigint
    set @maxsize =1
    exec sp_trace_create @trace_id output,2,@tracefile ,@maxsize
go

declare @trace_id int
   set @trace_id=2
declare @on bit
   set @on=2
declare @current_num int
   set @current_num =1
   while(@current_num <65)
  begin
     --add events to be traced, id 14 is the login event
     exec sp_trace_setevent @trace_id,14, @current_num,@on
  set @current_num=@current_num+1
  end
go

declare @trace_id int
    set @trace_id=2
declare @on bit
    set @on=2
declare @current_num int
    set @current_num =1
    while(@current_num <65)
    begin
      --add events to be traced, id 15 is the logout event
       exec sp_trace_setevent @trace_id,14, @current_num,@on
       set @current_num=@current_num+1
     end
go

Can I trace data by specifying sql server table instead of specifying file path? I do not want to use SQL Profiler.

Please provide me better solution of doing this.

Thanks.

Upvotes: 1

Views: 1525

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

No. Neither SQL Trace (your script example) nor Extended Events allows tracing directly to a table. This was a conscious design decision due to the overhead involved, which could impact server stability if a lot of data is captured.

However, you can trace to files and then import trace data into tables as desired. The fn_trace_gettable (for SQL Trace files) and sys.fn_xe_file_target_read_file (Extended Event file target) table-valued functions can be used as the source for an INSERT...SELECT.

Upvotes: 1

Related Questions