SQL Trace in SQL

Question

What is the use of SQL Trace in SQL

Aarti · Answer

Hi Lucy Refer below link to learn SQL Trace. IO hope it will help you. http://msdn.microsoft.com/en-us/library/ms191006.aspx Thanks.

Hemant Kumar · Answer

Some users want to know if there is a way to monitor events on SQL server without using SQL Profiler. Yes there is: the engine support behind SQL Profiler is the feature called SQL Trace which is introduced in SQL 2005. SQL Trace provides a set of stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within user's own applications to create traces manually and allows to write custom applications specific to their needs. The following sample code shows how to create customized SQL trace to monitor events to user's interest. -- sys.traces shows the existing sql traces on the server select * from sys . traces go --create a new trace make sure the @tracefile must NOT exist on the disk yet declare @tracefile nvarchar ( 500 ) set @tracefile = N'c:	emp
ewtraceFile' declare @trace_id int declare @maxsize bigint set @maxsize = 1 exec sp_trace_create @trace_id output  2  @tracefile  @maxsize go --- add the events of insterest to be traced and add the result columns of interest -- Note: look up in sys.traces to find the @trace_id here assuming this is the first trace in the server therefor @trace_id=1 declare @trace_id int set @trace_id = 1 declare @on bit set @on = 1 declare @current_num int set @current_num = 1 while ( @current_num 65 ) begin --add events to be traced id 14 is the login event you add other events per your own requirements the event id can be found @ BOL http://msdn.microsoft.com/en-us/library/ms186265.aspx exec sp_trace_setevent @trace_id  14  @current_num  @on set @current_num = @current_num + 1 end go --turn on the trace: status=1 -- use sys.traces to find the @trace_id here assuming this is the first trace in the server so @trace_id=1 declare @trace_id int set @trace_id = 1 exec sp_trace_setstatus @trace_id  1 --pivot the traced event select LoginName  DatabaseName * from :: fn_trace_gettable ( N'c:	emp
ewtraceFile.trc'  default ) go -- stop trace. Please manually delete the trace file on the disk -- use sys.traces to find the @trace_id here assuming this is the first trace in the server so @trace_id=1 declare @trace_id int set @trace_id = 1 exec sp_trace_setstatus @trace_id  0 exec sp_trace_setstatus @trace_id  2 go The granularity level of SQL trace is server-wide event groups which means it doesn't allow to auditing specific event or action by specific user in a specific database. If you need a more granulated audit mechanism you can start to look at the SQL Server Audit feature which is introduced in SQL Server 2008. http://sql-articles.com/articles/general/enable-disable-trace-flags-in-sql-server/