Step 1: Create a folder to store trace files, give R/W permissions to account under which you are planning run this trace and SQL Job. (Ex: D:/Trace)
Step 2: Create trace stored procedure using script below.
Step 3: Create a SQL Package to execute trace SP at desired time.
Trace Stored Prodedure
USE[AdventureWorksDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GenerateTrace]
(
@RunDuration int, -- In Minutes
@Folder nvarchar(200)
)
as
/*
Start a x 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. You might want to disable it when you are done running your scheduled trace.
Sample Command: EXEC DBO.SP_GenerateTrace 20,'D:\TRACE'
*/
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,@RunDuration,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 =50 -- 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 --+ convert(nvarchar,NEWID())
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
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @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, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @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, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
-- Set the Filters
declare @intfilter int
declare@bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1b567373-f19a-4add-9d49-d476171a2f0c'
--> Customization is now completed <--
-----------------------------------------------------------------------------
-- This filter is added to exclude all profiler traces.
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'
-- Set the trace status to start
select 'Trace id = ',@TraceID, 'Path=', @Folder+@StartDatetime+'\'
select 'To Stop this trace sooner, execute these two commands'
select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 0; -- Stop/pause Trace'
select ' EXEC sp_trace_setstatus @traceid = ' , @TraceID , ', @status = 2; -- Close trace and delete it from the server'
return
Creating a SQL Job to run trace
Step 1: Open SQL Server Management studio and expand SQL Server Agent, Then Jobs.
(Make sure that SQL Server Agent is started)
Step 2: Now Right Click on Jobs folder and say “New Job”.
This will open window shown below, specify name for your new job.
Step 3: Go to steps tab. Click “New” to get window shown below.
Give some name to step, Choose Type as “Transact SQL Script (T-SQL)”, Run as = Network Service, Database = “DB against which trace has to be run, this can be linked server as well”.
Finally, Copy this command for running trace SP into command section and press OK.
EXEC DBO.SP_GenerateTrace 5, 'D:\TRACE'
Note: This command runs trace for 5 Minutes from time job is started. Change this as per your requirement.
Step 4: Schedule Job to run at desired time by going to “schedule” tab and clicking “New”. This will bring up screen below which is self explainatory.
No comments:
Post a Comment