Clayton,
segue um script de exemplo que usei recentemente
/***************************************************
DEFAULT SERVER TRACE SIDE
=========================
MONITORA OS LOGIN\LOGOUT
***************************************************/
use master
go
drop procedure #tmpPPEventEnable
GO
create procedure #tmpPPEventEnable @TraceID int, @iEventID int
as
begin
set nocount on
declare @iColID int
declare @iColIDMax int
declare @on bit
set @on= 1
set @iColID = 1
set @iColIDMax = 64
while(@iColID <= @iColIDMax)
begin
exec sp_trace_setevent @TraceID, @iEventID, @iColID, @on
set @iColID = @iColID + 1
end
end
GO
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @InsertFileNameHere nvarchar(400)
-- *** DEFINE VARIÁVEIS E SEUS RESPECTIVOS VALORES ***
SET @DateTime = '2010-06-09 10:30:00' -- Informe o horário de término do trace
SET @InsertFileNameHere = 'D:\Monit_Login_Logout_' -- Informe o caminho onde o arquivo de trace deve ser salvo
SET @maxfilesize = 250 -- Tamanho máximo do arquivo de trace. Rollover a cada 250 MB
exec @rc = sp_trace_create @TraceID output, 2, @InsertFileNameHere, @maxfilesize, @DateTime
if (@rc != 0) goto error
declare @off bit
set @off = 0
declare @strVersion varchar(10)
set @strVersion = cast(SERVERPROPERTY('ProductVersion') as varchar(10))
if( (select cast( substring(@strVersion, 0, charindex('.', @strVersion)) as int)) >= 9)
begin
exec sp_trace_setevent @TraceID, 10, 1, @off -- No Text for RPC, only Binary for performance
exec sp_trace_setevent @TraceID, 11, 1, @off -- No Text for RPC, only Binary for performance
end
exec #tmpPPEventEnable @TraceID, 17 -- Existing Connection
exec #tmpPPEventEnable @TraceID, 14 -- Audit Login
exec #tmpPPEventEnable @TraceID, 15 -- Audit Logout
-- Set the Filters
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%' -- eliminando conexao do profiler
exec sp_trace_setfilter @TraceID, 35, 0, 6, N'dbTeste' -- nome do banco 1
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'dbTeste2'-- nome do banco 2
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'dbTeste3'-- nome do banco 3
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
print '--Para parar o trace execute os seguintes comandos'
print 'exec sp_trace_setstatus ' + cast(@TraceID as varchar) + ', 0'
print 'exec sp_trace_setstatus ' + cast(@TraceID as varchar) + ', 2'
Print ''
Print '-- Para consultar se o trace está rodando execute o seguinte comando'
Print 'select * from ::fn_trace_getinfo(default)'
goto finish
error:
select ErrorCode=@rc
finish:
drop procedure #tmpPPEventEnable
select * from ::fn_trace_getinfo(default)
go
att.
Marcelo Fernandes
MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
