Create trace with tsql

we can use sql server profiler to create a trace with a UI, sometimes we want to do this automatically, such as in a agent job. so we need another way.in fact ,the sql server 2008 can expert the tsql code for the trace in the file menu.

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[StartProfileTrace]    Script Date: 04/26/2012 23:45:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: SQLSERVER 2008R2
-- Description:  Start the Profile Trace
-- =============================================
DROP PROCEDURE [dbo].[StartProfileTrace]
GO
CREATE PROCEDURE [dbo].[StartProfileTrace]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @fileName NVARCHAR(255)
declare @sql NVARCHAR(MAX)
set @maxfilesize = 500
set @fileName = N'c:\tracelog\' + CONVERT(NVARCHAR(12), GETUTCDATE(), 112) + '_' + CONVERT(NVARCHAR(255),NEWID())
set @sql = ''

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 2, @fileName, @maxfilesize, NULL

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 35, @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, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 48, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 7, @on
--just capture the needed database
SELECT @sql = @sql + 'exec sp_trace_setfilter ' + CAST(@TraceID AS NVARCHAR(50)) + ', 35,0,1,N''' + name + ''''+CHAR(13)+CHAR(10)
FROM sys.databases
where name not in ('databasename', 'tempdb', 'master')
 
EXEC sp_executesql @sql

SET @sql=''
-- Login name
SELECT @sql = @sql + 'exec sp_trace_setfilter ' + CAST(@TraceID AS NVARCHAR(50)) + ', 11,0,1,N''' + loginname + ''''+CHAR(13)+CHAR(10)
FROM
sys.syslogins 
WHERE loginname NOT IN ('FAREAST\v-evanya','NT AUTHORITY\SYSTEM')
EXEC sp_executesql @sql

-- Duration
--declare @bigintfilter bigint
--set @bigintfilter = 5000000
--exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID

END


GO

 start and stop the trace.

exec [StartProfileTrace]

DECLARE @id int=0

SET @id= 2 -- Put traceId here

EXEC sp_trace_setstatus @id, 0 -- Stop

EXEC sp_trace_setstatus @id, 2 -- Delete

 you can get more details about the eventclass to filter:

http://msdn.microsoft.com/en-us/library/ms186265.aspx

转载于:https://www.cnblogs.com/huaxiaoyao/archive/2012/05/04/2482140.html