Saturday, June 18, 2011

Setup Black Box in SQL Server

Check out the below script to trace. This will capture all the SQL Server statements and activities. Be extra cautious as this will take significant space which depends on SQL Server activities. This trace will be created in default sql server Data Directory, this case c:\Program files, as we install SQL Serever 

--  Create a Trace file, which will call all SQL Server events and Activities */
declare @rc int, @traceid int
exec @rc = sp_trace_create @traceid output, 8
select traceid = @traceid, error = @rc
exec sp_trace_setstatus @traceid, 1
-- -- Select the TraceId, which is initiated by SQL Server, this number will be used to stop the trace
select * from ::fn_trace_getinfo(@traceid)

-- We are using 2, as a traceID which was started in last statement, 
sp_trace_setstatus 2, 0
sp_trace_setstatus 2, 2

No comments:

Post a Comment