USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_start] Script Date: 2017/7/6 19:48:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
create PROC [dbo].[sp_perfworkload_trace_start] @dbid AS INT, @tracefile AS NVARCHAR(254), @traceid AS INT OUTPUT AS -- Create a Queue DECLARE @rc AS INT; DECLARE @maxfilesize AS BIGINT;
SET @maxfilesize = 5;
EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL IF (@rc != 0) GOTO error;
-- Client side File and Table cannot be scripted
-- Set the events DECLARE @on AS BIT; SET @on = 1; EXEC sp_trace_setevent @traceid, 10, 15, @on; EXEC sp_trace_setevent @traceid, 10, 8, @on; EXEC sp_trace_setevent @traceid, 10, 16, @on; EXEC sp_trace_setevent @traceid, 10, 48, @on; EXEC sp_trace_setevent @traceid, 10, 1, @on; EXEC sp_trace_setevent @traceid, 10, 17, @on; EXEC sp_trace_setevent @traceid, 10, 10, @on; EXEC sp_trace_setevent @traceid, 10, 18, @on; EXEC sp_trace_setevent @traceid, 10, 11, @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, 45, 8, @on; 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, 10, @on; EXEC sp_trace_setevent @traceid, 45, 18, @on; EXEC sp_trace_setevent @traceid, 45, 11, @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, 8, @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, 10, @on; EXEC sp_trace_setevent @traceid, 41, 18, @on; EXEC sp_trace_setevent @traceid, 41, 11, @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;
-- Set the Filters DECLARE @intfilter AS INT; DECLARE @bigintfilter AS BIGINT; -- Application name filter EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%'; -- Database ID filter EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;
-- Set the trace status to start EXEC sp_trace_setstatus @traceid, 1;
-- Print trace id and file name for future references PRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))
GOTO finish;
error: PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));
finish:
declare @dbid int; declare @traceid int; SET @dbid = DB_ID(); EXEC dbo.sp_perfworkload_trace_start @dbid = @dbid, @tracefile = 'D:\sqltrc\20170706_6', @traceid = @traceid OUTPUT;
EXEC sp_trace_setstatus 2, 0; EXEC sp_trace_setstatus 2, 2;
SELECT * FROM sys.fn_trace_gettable( 'D:\sqltrc\20170706_6.trc', NULL) AS t ORDER BY StartTime desc