Create the Trace Definition
The most efficient means to define the SQL commands used for constructing a profiler trace is to use SQL Profiler.
* Start SQL Profiler and select File --> New Trace.
-Select the option of events, columns, and filters you want in your trace.
* Start the trace and then stop it.
* to check the script file Click File > Export > Script Trace Definition > For SQL Server 2005
* Save the trace file on local disk.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[trc_DBNAME_PROD] Script Date: 10/30/2011 21:06:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[trc_DBNAME_PROD] @Folder nvarchar(200)
as
/*
Start a 120 minute profiler trace storing the captured output in provider folder.
The folder must exist. A subfolder will be created using the start date and time to allow for repeated running of this profile without replacing the previuos captured trace files. On SQL Server 2005, XP_CMDSHELL needs to be enable to create the subfolder.
*/
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,120,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 1024 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--> By your saved trace file…..<--
-- Set the events… add the events under this section
/* for example please see the below format
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
-->By your saved trace file…..-
-- Set the events… add the events under this section
/* for example please see the below format
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 18
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HC1APAU1P'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%Profiler%'
exec sp_trace_setfilter @TraceID, 11, 0, 0, N'CDS_service'
--exec sp_trace_setfilter @TraceID, 11, 0, 0, N'test'
exec sp_trace_setfilter @TraceID, 35, 0, 0, N'CDSAML_PROD'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
· Automation process is completed
· Please create a store procedure according to your naming conventions
1) Please create a JOB with the below script
2) USE [master]
3) GO
4)
5) DECLARE @return_value int
6)
7) EXEC @return_value = [dbo].[trc_DBNAME_PROD]
8) @Folder = N'E:\ProfilerTrace\Template'
9)
10)SELECT 'Return Value' = @return_value
11)
12) GO
Please select the your desired location to @ Folder = ‘*******’
1) If you want to do manual stop, u need to execute the below script
select * FROM ::fn_trace_getinfo(default) –- to find the running trace ID’s.. please don’t stop trace ID 1 because its default treace
EXEC sp_trace_setstatus @traceid = 2, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = 2, @status = 2; -- Close trace and delete it from the server
NOTE***: Please enable XP_CMDSHELL, bocz the above script will create folders on local disk...
Thanks,
Satish Kumar.
