Trace Sql senza Profiler e senza installazione di software aggiuntivo

Ecco come effettuare il trace dell'attività SQL su un SQl server in cui non è disponibile il SQL profiler.

Gli script sono tratti dal sito 
http://www.mssqltips.com/tip.asp?tip=1035

Il trace creato può essere gestito tramite le seguenti istruzioni:

EXEC sp_trace_setstatus 1, 1 --abilita il trace

EXEC sp_trace_setstatus 1, 0 --disabilita il trace

EXEC sp_trace_setstatus 1, 2 --cancella il trace e salva su file

--numero trace attive:

SELECT * FROM :: fn_trace_getinfo(default)

--importazione in una tabella del file di trace

drop table sqlTableToLoad 

SELECT * INTO sqlTableToLoad 

FROM ::fn_trace_gettable(N'c:\TestTrace.trc', DEFAULT) 

--query varie 

select * from sqlTableToLoad  where textdata like '%select%'

select * from sqlTableToLoad  where textdata like '%Chiamate%'

/***************************************************************/

-- abilitazione trace

-- da errore -12  se il file c:\TestTrace.trc non è stato eliminato

/**************************************************************/

-- Declare variables 

DECLARE @rc INT 

DECLARE @TraceID INT 

DECLARE @maxFileSize bigint 

DECLARE @fileName NVARCHAR(128) 

DECLARE @on bit 

-- Set values 

SET @maxFileSize = 5 

SET @fileName = N'C:\TestTrace' 

SET @on = 1 

-- Create trace 

EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL  

-- If error end process 

IF (@rc != 0) GOTO error 

-- Set the events and data to collect 

EXEC sp_trace_setevent @TraceID, 45,  1, @on 

EXEC sp_trace_setevent @TraceID, 45, 12, @on 

EXEC sp_trace_setevent @TraceID, 45, 13, @on 

EXEC sp_trace_setevent @TraceID, 45, 14, @on 

EXEC sp_trace_setevent @TraceID, 45, 15, @on 

EXEC sp_trace_setevent @TraceID, 45, 16, @on 

EXEC sp_trace_setevent @TraceID, 45, 17, @on 

EXEC sp_trace_setevent @TraceID, 10,  1, @on 

EXEC sp_trace_setevent @TraceID, 10, 12, @on 

EXEC sp_trace_setevent @TraceID, 10, 13, @on 

EXEC sp_trace_setevent @TraceID, 10, 14, @on 

EXEC sp_trace_setevent @TraceID, 10, 15, @on 

EXEC sp_trace_setevent @TraceID, 10, 16, @on 

EXEC sp_trace_setevent @TraceID, 10, 17, @on

EXEC sp_trace_setevent @TraceID, 12, 1, @on

EXEC sp_trace_setevent @TraceID, 12, 2, @on

EXEC sp_trace_setevent @TraceID, 12, 3, @on

EXEC sp_trace_setevent @TraceID, 12, 4, @on

EXEC sp_trace_setevent @TraceID, 12, 5, @on

EXEC sp_trace_setevent @TraceID, 12, 6, @on

EXEC sp_trace_setevent @TraceID, 12, 7, @on

EXEC sp_trace_setevent @TraceID, 12, 8, @on

EXEC sp_trace_setevent @TraceID, 12, 9, @on

EXEC sp_trace_setevent @TraceID, 12, 10, @on

EXEC sp_trace_setevent @TraceID, 12, 11, @on

EXEC sp_trace_setevent @TraceID, 12, 12, @on

EXEC sp_trace_setevent @TraceID, 12, 13, @on

EXEC sp_trace_setevent @TraceID, 12, 14, @on

EXEC sp_trace_setevent @TraceID, 12, 15, @on

EXEC sp_trace_setevent @TraceID, 12, 16, @on

EXEC sp_trace_setevent @TraceID, 12, 17, @on

-- Set Filters 

-- filter1 include databaseId = 6 

--EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6 

-- filter2 exclude application SQL Profiler 

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' 

-- Start the trace 

EXEC sp_trace_setstatus @TraceID, 1 

  

-- display trace id for future references  

SELECT TraceID=@TraceID  

GOTO finish  

-- error trap 

error:  

SELECT ErrorCode=@rc  

-- exit 

finish:  

GO

/*******************************************************/