Think of this as a lightweight alternative to SQL profiler. Under the hood of SQL profiler, there exists SQL Trace -- which provides a collection of stored procedures to generate trace info. Cut and paste the t-script below to see how it works.
-- Pay attention to what the server settings for traces look like first:
select * from sys.traces
go
-- you are looking to make sure there isn't already a trace file set somewhere
-- In any case you will need to create a new trace, make sure the @tracefile doesn't exist on the disk yet
declare @myTracefile nvarchar(500) set @tracefile=N'c:\temp\myTraceFile.trc'
declare @myTrace_id int
declare @maxsize bigint
set @maxsize =1
exec sp_trace_create @myTrace_id output,2,@MyTracefile ,@maxsize
go
--- add the result columns you care about
-- if you don't have any other traces set, you'll be set to 1, if not, run the select * sys.traces again to see
-- what got assigned. look up in sys.traces to find the @mytrace_id,
-- in this example, I will assume its 1 for now so that is why you see: @mytrace_id=1
declare @myTrace_id int
set @myTrace_id=1
declare @xon bit
set @xon=1
declare @current int
set @current =1
while(@current <10) -- 10 times is just an arbitrary number of times i might log something
-- pick whatever you want
begin
-- here is where you figure out what events you want to log to the file.
-- Go here to decide: https://msdn.microsoft.com/en-us/library/ms186265.aspx
-- Try it out for now, using #14 -- that's probably not what you want, but get it to work first.
exec sp_trace_setevent @myTrace_id,14, @current,@xon
set @current=@current+1
-- later you'll want to look at it
declare @myTrace_id int
set @myTrace_id=1
exec sp_trace_setstatus @myTrace_id,1
-- see the traced event
select yourlogin, dbinstancename,* from ::fn_trace_gettable(N'myTraceFile.trc',default)
go
-- Once you're done, you'll need to cleanup
-- stop the trace and delete the file
declare @myTrace_id int
set @myTrace_id=1
exec sp_trace_setstatus @myTrace_id,0
exec sp_trace_setstatus @myTrace_id,2 -- delete def from server
go