Bookmark and Share

Friday, February 06, 2009

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
/*******************************************************/