Creating SQL Agent Job to Run Trace Script

I am trying to run a Trace script and schedule it to run during weekdays 6am
to 6pm.
I thought I could use the CMDEXEC and run the script in osql. This works
except that the job stops immediatley after executing. (I need the trace to
run all day not for 1 second) Is there a way to work around this using
CMDEXEC?
Bill PapanestorYou have to use trace special procedure to set it up. Use the following.
Remember the trace needs to be stopped using the handle number it is running
on. You have the way to do it in the header of the procedure
This Will start a trace (Profiler) that will log in trc file queries that
last more than 5 seconds.
Hope this helps
Andre
USE master
GO
if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp__TraceActivity]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp__TraceActivity]
GO
if exists (select * from sysobjects where id =
object_id(N'[dbo].[sp__TraceQuery]') and OBJECTPROPERTY(id, N'IsProc
edure') =
1)
drop procedure [dbo].[sp__TraceQuery]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC dbo.sp__TraceQuery @.FilePath nvarchar(128) =
'C:\SQLSERVERINFO',@.NotThisAppFilter varchar(255) = 'SQL Server Profiler%;MS
SQLEM%;SQL Query Analyzer%',@.MinDurationFilter int = 5000,@.StopIt bit = NULL
AS
/ ****************************************
*****
** Creation Date: 05/29/2002
** Created By : avigneau
** Database : master
** Description : Will start a trace (Profiler) that will log in trc file
queries that last more than 5 seconds.
** Parameters :
** Compatibility: SQL Server 7.0, 2000
** Remark :
** Example : Hit the "Run" button or press F5
-- TO uninstall RUN THIS in master:
USE master
EXECUTE dbo.sp__TraceQuery @.StopIt = 1
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Trace - Trace File
Generation'
DROP PROCEDURE dbo.sp__TraceQuery
****************************************
*****/
SET NOCOUNT ON
DECLARE @.xFlag int
DECLARE @.queue_handle int
DECLARE @.column_value int
DECLARE @.cnt int,@.vc varchar(64)
IF @.StopIt IS NULL OR @.FilePath IS NULL
BEGIN
PRINT 'Usage:'
PRINT 'EXEC dbo.sp__TraceActivity @.FilePath =
''C:\SQLSERVERINFO'',@.NotThisAppFilter = ''SQL Server Profiler%;SQLAgent%;MS
SQLEM%;SQL Query Analyzer%'',@.MinDurationFilter = 5000,@.StopIt = 0'
PRINT ' |
|
| |'
PRINT ' where you want the file created
|
| |'
PRINT '
What application you want to exclude from the trace
| |'
PRINT '
Minimum duration of
traced queries |'
PRINT '
0 will start the traces 1 will stop it'
RETURN
END
-- DB & OS Version control START
DECLARE @.SQLVersion dec(4,2), @.OSVersion dec(4,2)
CREATE TABLE #Version (IndexId int NOT NULL
,Name varchar(60)
,Internal_Value int NULL
,Character_Value varchar(255) NULL)
INSERT #Version exec master.dbo.xp_msver
SELECT @.SQLVersion = CONVERT(dec(4,2),SUBSTRING(Character_Val
ue,1,4))
FROM #Version
WHERE Name = 'ProductVersion'
SELECT @.OSVersion = CONVERT(dec(4,2),SUBSTRING(Character_Val
ue,1,4))
FROM #Version
WHERE Name = 'WindowsVersion'
DROP TABLE #Version
-- DB & OS Version control END
-- to check if PATH directory exists
SELECT @.FilePath = REPLACE(@.FilePath,'"','')
IF SUBSTRING(REVERSE(@.FilePath),1,1) <> ''
SELECT @.FilePath = @.FilePath+''+@.@.SERVERNAME+''
CREATE TABLE #PATHEXIST (col1 bit, col2 bit, col3 bit)
INSERT #PATHEXIST EXEC master.dbo.xp_fileexist @.FilePath
IF (SELECT col2 FROM #PATHEXIST) = 0
BEGIN
SET @.FilePath = 'MD '+@.FilePath
EXEC master.dbo.xp_cmdshell @.FilePath
SET @.FilePath = REPLACE(@.FilePath,'MD ','')
END
IF @.SQLVersion = 7.00
BEGIN
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME='SQLTRACEHANDLE')
BEGIN
SET @.FilePath =
@.FilePath+'TRACE'+CONVERT(varchar(2),DAT
EPART(hh,GETDATE()))+'.trc'
SELECT @.queue_handle = Handle FROM SQLTRACEHANDLE
EXEC xp_trace_destroyqueue @.queue_handle
IF @.StopIt = 1
BEGIN
-- DROP TABLE dbo.SQLTRACEHANDLE
RETURN
END
DELETE SQLTRACEHANDLE
END
ELSE
CREATE TABLE dbo.SQLTRACEHANDLE (Handle int, Location varchar(255))
SET @.column_value = 2048|1024|512|128|4|1|4096|8192|16384|32
768|65536|131072
-- to delete the old file
EXEC master.dbo.xp_fileexist @.FilePath,@.xFlag OUTPUT-- @.FilePath
IF @.xFlag <> 0
BEGIN
SET @.FilePath = 'DEL '+@.FilePath
EXEC master.dbo.xp_cmdshell @.FilePath
SET @.FilePath = REPLACE(@.FilePath,'DEL ','')
END
EXEC xp_trace_addnewqueue 1000,5,95,90,@.column_value,@.queue_handle
OUTPUT
--To look up the names, execute xp_trace_geteventnames
EXEC xp_trace_seteventclassrequired @.queue_handle, 10, 1 --RPC:Completed
--EXEC xp_trace_seteventclassrequired @.queue_handle, 11, 1 --RPC:Starting
EXEC xp_trace_seteventclassrequired @.queue_handle, 12, 1 --SQL:BatchComplete
d
--EXEC xp_trace_seteventclassrequired @.queue_handle, 13, 1
--SQL:BatchStarting
--EXEC xp_trace_seteventclassrequired @.queue_handle, 14, 1 --Connect
--EXEC xp_trace_seteventclassrequired @.queue_handle, 16, 1 --Disconnect
--EXEC xp_trace_seteventclassrequired @.queue_handle, 17, 1
--ExistingConnection
--EXEC xp_trace_seteventclassrequired @.queue_handle, 40, 1 --SQL StmtStartin
g
EXEC xp_trace_seteventclassrequired @.queue_handle, 41, 1 --SQL Stmtcompleted
--EXEC xp_trace_seteventclassrequired @.queue_handle, 44, 1 --SP StmtStarting
EXEC xp_trace_seteventclassrequired @.queue_handle, 45, 1 --SP Stmtcompleted
--Set any filters. (Don't trace the Profiler).
EXEC xp_trace_setappfilter @.queue_handle, NULL, @.NotThisAppFilter
EXEC xp_trace_setdurationfilter @.queue_handle, @.MinDurationFilter, 0
--Configure the queue to write to a file.
EXEC xp_trace_setqueuedestination @.queue_handle,2,1,NULL,@.FilePath
--Start the consumer that actually writes to a file.
EXEC xp_trace_startconsumer @.queue_handle
--Display the queue handle. It will be needed
--later to stop the queue.
INSERT SQLTRACEHANDLE(Handle,Location) SELECT @.queue_handle,@.FilePath
--To Save the queue definition
--EXEC xp_trace_savequeuedefinition @.queue_handle,'Diag',1
END
ELSE IF @.SQLVersion = 8.00
BEGIN
-- SQL Server 2000 only
DECLARE @.eventid int,@.bon bit,@.nvcvalue nvarchar(128)
SET @.FilePath =
@.FilePath+'TRACE'+CONVERT(varchar(2),DAT
EPART(hh,GETDATE()))+'.trc'
SELECT @.bon = 1
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME='SQLTRACEHANDLE')
BEGIN
SELECT @.queue_handle = Handle FROM SQLTRACEHANDLE
IF @.queue_handle IS NOT NULL
BEGIN
EXEC sp_trace_setstatus @.queue_handle,0
EXEC sp_trace_setstatus @.queue_handle,2
END
IF @.StopIt = 1
BEGIN
-- DROP TABLE dbo.SQLTRACEHANDLE
RETURN
END
DELETE SQLTRACEHANDLE
END
ELSE
CREATE TABLE dbo.SQLTRACEHANDLE (Handle int, Location varchar(255))
-- to delete the old file
EXEC master.dbo.xp_fileexist @.FilePath,@.xFlag OUTPUT-- @.FilePath
IF @.xFlag <> 0
BEGIN
SET @.FilePath = 'DEL '+@.FilePath
EXEC master.dbo.xp_cmdshell @.FilePath
SET @.FilePath = REPLACE(@.FilePath,'DEL ','')
END
SET @.FilePath = REPLACE(@.FilePath,'.trc','')
EXEC sp_trace_create @.queue_handle = @.queue_handle OUTPUT
, @.options = 2--option_value
, @.tracefile = @.FilePath--'trace_file'
, @.maxfilesize = NULL--max_file_size
, @.stoptime = NULL--'stop_time'
INSERT SQLTRACEHANDLE(Handle,Location) SELECT @.queue_handle,@.FilePath+'.trc'
-- to set what to trace
SELECT @.eventid = 10
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 1--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 3--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 8--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 10--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 11--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 12--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 13--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 14--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 15--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 16--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 17--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 18--column_id
, @.on = @.bon
SELECT @.eventid = 41
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 1--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 3--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 8--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 10--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 11--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 12--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 13--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 14--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 15--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 16--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 17--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 18--column_id
, @.on = @.bon
SELECT @.eventid = 43
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 1--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 3--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 8--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 10--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 11--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 12--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 13--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 14--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 15--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 16--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 17--column_id
, @.on = @.bon
EXEC sp_trace_setevent @.queue_handle = @.queue_handle
, @.eventid = @.eventid--event_id
, @.columnid = 18--column_id
, @.on = @.bon
SELECT @.cnt = 1
IF SUBSTRING(@.NotThisAppFilter,1,1) = ';'
SELECT @.NotThisAppFilter =
SUBSTRING(@.NotThisAppFilter,2,LEN(@.NotTh
isAppFilter)-1)
WHILE @.cnt < LEN(@.NotThisAppFilter)
BEGIN
SELECT @.cnt = PATINDEX('%;%',@.NotThisAppFilter)
IF @.cnt = 0
BEGIN
SELECT @.nvcvalue = @.NotThisAppFilter
EXEC sp_trace_setfilter @.queue_handle = @.queue_handle
, @.columnid = 10 --column_id
, @.logical_operator = 0--logical_operator
, @.comparison_operator = 7--comparison_operator
, @.value = @.nvcvalue--value
BREAK
END
SELECT @.nvcvalue = SUBSTRING(@.NotThisAppFilter,1,@.cnt-1)
SELECT @.NotThisAppFilter =
SUBSTRING(@.NotThisAppFilter,@.cnt+1,LEN(@.
NotThisAppFilter)-@.cnt+1)
EXEC sp_trace_setfilter @.queue_handle = @.queue_handle
, @.columnid = 10 --column_id
, @.logical_operator = 0--logical_operator
, @.comparison_operator = 7--comparison_operator
, @.value = @.nvcvalue--value
END
SELECT @.nvcvalue = @.queue_handle,@.vc=@.MinDurationFilter
EXEC('DECLARE @.bi bigint
SELECT @.bi = '+@.vc+'
EXEC sp_trace_setfilter @.queue_handle = '+@.nvcvalue+'
, @.columnid = 13 --column_id
, @.logical_operator = 0--logical_operator
, @.comparison_operator = 4--comparison_operator
, @.value = @.bi')
-- to start it
EXEC sp_trace_setstatus @.queue_handle,1
END
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
-- Script generated on 5/27/02 12:48 PM
-- By: \Administrator
-- Server: DATABASE1
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Trace
Monitoring') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Trace Monitoring'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Trace - Trace File Generation')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Trace - Trace File Generation''
since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Trace - Trace File
Generation'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Trace - Trace File Generation', @.owner_login_name = N'sa',
@.description = N'No description available.', @.category_name = N'Trace
Monitoring', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'Step 1', @.command = N'EXEC master.dbo.sp__TraceQuery
@.FilePath = ''C:\SQLSERVERINFO'',@.NotThisAppFilter = ''SQL Server
Profiler%;MS SQLEM%;SQL Query Analyzer%'',@.MinDurationFilter = 5000,@.StopIt
=
0', @.database_name = N'master', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20020513
,
@.active_start_time = 0, @.freq_interval = 1, @.freq_subday_type = 8,
@.freq_subday_interval = 1, @.freq_relative_interval = 0,
@.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time =
235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
"Bill Papanestor" wrote:

> I am trying to run a Trace script and schedule it to run during weekdays 6
am
> to 6pm.
> I thought I could use the CMDEXEC and run the script in osql. This works
> except that the job stops immediatley after executing. (I need the trace t
o
> run all day not for 1 second) Is there a way to work around this using
> CMDEXEC?
>
> --
> Bill Papanestor|||This is great!!!
Thanks for your help
"Andre Vigneau" wrote:
[vbcol=seagreen]
> You have to use trace special procedure to set it up. Use the following.
> Remember the trace needs to be stopped using the handle number it is runni
ng
> on. You have the way to do it in the header of the procedure
> This Will start a trace (Profiler) that will log in trc file queries that
> last more than 5 seconds.
> Hope this helps
> Andre
> USE master
> GO
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[sp__TraceActivity]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[sp__TraceActivity]
> GO
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[sp__TraceQuery]') and OBJECTPROPERTY(id, N'IsPr
ocedure') =
> 1)
> drop procedure [dbo].[sp__TraceQuery]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROC dbo.sp__TraceQuery @.FilePath nvarchar(128) =
> 'C:\SQLSERVERINFO',@.NotThisAppFilter varchar(255) = 'SQL Server Profiler%;
MS
> SQLEM%;SQL Query Analyzer%',@.MinDurationFilter int = 5000,@.StopIt bit = NU
LL
> AS
> / ****************************************
*****
> ** Creation Date: 05/29/2002
> ** Created By : avigneau
> ** Database : master
> ** Description : Will start a trace (Profiler) that will log in trc file
> queries that last more than 5 seconds.
> ** Parameters :
> ** Compatibility: SQL Server 7.0, 2000
> ** Remark :
> ** Example : Hit the "Run" button or press F5
> -- TO uninstall RUN THIS in master:
> USE master
> EXECUTE dbo.sp__TraceQuery @.StopIt = 1
> EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Trace - Trace File
> Generation'
> DROP PROCEDURE dbo.sp__TraceQuery
> ****************************************
*****/
> SET NOCOUNT ON
> DECLARE @.xFlag int
> DECLARE @.queue_handle int
> DECLARE @.column_value int
> DECLARE @.cnt int,@.vc varchar(64)
> IF @.StopIt IS NULL OR @.FilePath IS NULL
> BEGIN
> PRINT 'Usage:'
> PRINT 'EXEC dbo.sp__TraceActivity @.FilePath =
> ''C:\SQLSERVERINFO'',@.NotThisAppFilter = ''SQL Server Profiler%;SQLAgent%;
MS
> SQLEM%;SQL Query Analyzer%'',@.MinDurationFilter = 5000,@.StopIt = 0'
> PRINT ' |
> |
> | |'
> PRINT ' where you want the file created
> |
> | |'
> PRINT '
> What application you want to exclude from the trace
> | |'
> PRINT '
> Minimum duration
of
> traced queries |'
> PRINT '
> 0 will start the traces 1 will stop it'
> RETURN
> END
> -- DB & OS Version control START
> DECLARE @.SQLVersion dec(4,2), @.OSVersion dec(4,2)
> CREATE TABLE #Version (IndexId int NOT NULL
> ,Name varchar(60)
> ,Internal_Value int NULL
> ,Character_Value varchar(255) NULL)
> INSERT #Version exec master.dbo.xp_msver
> SELECT @.SQLVersion = CONVERT(dec(4,2),SUBSTRING(Character_Val
ue,1,4))
> FROM #Version
> WHERE Name = 'ProductVersion'
> SELECT @.OSVersion = CONVERT(dec(4,2),SUBSTRING(Character_Val
ue,1,4))
> FROM #Version
> WHERE Name = 'WindowsVersion'
> DROP TABLE #Version
> -- DB & OS Version control END
> -- to check if PATH directory exists
> SELECT @.FilePath = REPLACE(@.FilePath,'"','')
> IF SUBSTRING(REVERSE(@.FilePath),1,1) <> ''
> SELECT @.FilePath = @.FilePath+''+@.@.SERVERNAME+''
> CREATE TABLE #PATHEXIST (col1 bit, col2 bit, col3 bit)
> INSERT #PATHEXIST EXEC master.dbo.xp_fileexist @.FilePath
> IF (SELECT col2 FROM #PATHEXIST) = 0
> BEGIN
> SET @.FilePath = 'MD '+@.FilePath
> EXEC master.dbo.xp_cmdshell @.FilePath
> SET @.FilePath = REPLACE(@.FilePath,'MD ','')
> END
> IF @.SQLVersion = 7.00
> BEGIN
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
> TABLE_NAME='SQLTRACEHANDLE')
> BEGIN
> SET @.FilePath =
> @.FilePath+'TRACE'+CONVERT(varchar(2),DAT
EPART(hh,GETDATE()))+'.trc'
> SELECT @.queue_handle = Handle FROM SQLTRACEHANDLE
> EXEC xp_trace_destroyqueue @.queue_handle
> IF @.StopIt = 1
> BEGIN
> -- DROP TABLE dbo.SQLTRACEHANDLE
> RETURN
> END
> DELETE SQLTRACEHANDLE
> END
> ELSE
> CREATE TABLE dbo.SQLTRACEHANDLE (Handle int, Location varchar(255))
> SET @.column_value = 2048|1024|512|128|4|1|4096|8192|16384|32
768|65536|131
072
> -- to delete the old file
> EXEC master.dbo.xp_fileexist @.FilePath,@.xFlag OUTPUT-- @.FilePath
> IF @.xFlag <> 0
> BEGIN
> SET @.FilePath = 'DEL '+@.FilePath
> EXEC master.dbo.xp_cmdshell @.FilePath
> SET @.FilePath = REPLACE(@.FilePath,'DEL ','')
> END
> EXEC xp_trace_addnewqueue 1000,5,95,90,@.column_value,@.queue_handle
OUTPUT
> --To look up the names, execute xp_trace_geteventnames
> EXEC xp_trace_seteventclassrequired @.queue_handle, 10, 1 --RPC:Completed
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 11, 1 --RPC:Starting
> EXEC xp_trace_seteventclassrequired @.queue_handle, 12, 1 --SQL:BatchCompl
eted
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 13, 1
> --SQL:BatchStarting
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 14, 1 --Connect
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 16, 1 --Disconnect
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 17, 1
> --ExistingConnection
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 40, 1 --SQL StmtStar
ting
> EXEC xp_trace_seteventclassrequired @.queue_handle, 41, 1 --SQL Stmtcomple
ted
> --EXEC xp_trace_seteventclassrequired @.queue_handle, 44, 1 --SP StmtStart
ing
> EXEC xp_trace_seteventclassrequired @.queue_handle, 45, 1 --SP Stmtcomplet
ed
> --Set any filters. (Don't trace the Profiler).
> EXEC xp_trace_setappfilter @.queue_handle, NULL, @.NotThisAppFilter
> EXEC xp_trace_setdurationfilter @.queue_handle, @.MinDurationFilter, 0
> --Configure the queue to write to a file.
> EXEC xp_trace_setqueuedestination @.queue_handle,2,1,NULL,@.FilePath
> --Start the consumer that actually writes to a file.
> EXEC xp_trace_startconsumer @.queue_handle
> --Display the queue handle. It will be needed
> --later to stop the queue.
> INSERT SQLTRACEHANDLE(Handle,Location) SELECT @.queue_handle,@.FilePath
> --To Save the queue definition
> --EXEC xp_trace_savequeuedefinition @.queue_handle,'Diag',1
> END
> ELSE IF @.SQLVersion = 8.00
> BEGIN
> -- SQL Server 2000 only
> DECLARE @.eventid int,@.bon bit,@.nvcvalue nvarchar(128)
> SET @.FilePath =
> @.FilePath+'TRACE'+CONVERT(varchar(2),DAT
EPART(hh,GETDATE()))+'.trc'
> SELECT @.bon = 1
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
> TABLE_NAME='SQLTRACEHANDLE')
> BEGIN
> SELECT @.queue_handle = Handle FROM SQLTRACEHANDLE
> IF @.queue_handle IS NOT NULL
> BEGIN
> EXEC sp_trace_setstatus @.queue_handle,0
> EXEC sp_trace_setstatus @.queue_handle,2
> END
> IF @.StopIt = 1
> BEGIN
> -- DROP TABLE dbo.SQLTRACEHANDLE
> RETURN
> END
> DELETE SQLTRACEHANDLE
> END
> ELSE
> CREATE TABLE dbo.SQLTRACEHANDLE (Handle int, Location varchar(255))
> -- to delete the old file
> EXEC master.dbo.xp_fileexist @.FilePath,@.xFlag OUTPUT-- @.FilePath
> IF @.xFlag <> 0
> BEGIN
> SET @.FilePath = 'DEL '+@.FilePath
> EXEC master.dbo.xp_cmdshell @.FilePath
> SET @.FilePath = REPLACE(@.FilePath,'DEL ','')
> END
>
> SET @.FilePath = REPLACE(@.FilePath,'.trc','')
> EXEC sp_trace_create @.queue_handle = @.queue_handle OUTPUT
> , @.options = 2--option_value
> , @.tracefile = @.FilePath--'trace_file'
> , @.maxfilesize = NULL--max_file_size
> , @.stoptime = NULL--'stop_time'
> INSERT SQLTRACEHANDLE(Handle,Location) SELECT @.queue_handle,@.FilePath+'.t
rc'
> -- to set what to trace
> SELECT @.eventid = 10
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 1--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 3--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 8--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 10--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 11--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 12--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 13--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 14--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 15--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 16--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 17--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 18--column_id
> , @.on = @.bon
> SELECT @.eventid = 41
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 1--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 3--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 8--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 10--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 11--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 12--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 13--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 14--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 15--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 16--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 17--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 18--column_id
> , @.on = @.bon
> SELECT @.eventid = 43
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 1--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 3--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 8--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 10--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 11--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 12--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 13--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 14--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 15--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 16--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 17--column_id
> , @.on = @.bon
> EXEC sp_trace_setevent @.queue_handle = @.queue_handle
> , @.eventid = @.eventid--event_id
> , @.columnid = 18--column_id
> , @.on = @.bon
> SELECT @.cnt = 1
> IF SUBSTRING(@.NotThisAppFilter,1,1) = ';'
> SELECT @.NotThisAppFilter =
> SUBSTRING(@.NotThisAppFilter,2,LEN(@.NotTh
isAppFilter)-1)
> WHILE @.cnt < LEN(@.NotThisAppFilter)
> BEGIN
> SELECT @.cnt = PATINDEX('%;%',@.NotThisAppFilter)
> IF @.cnt = 0
> BEGIN
> SELECT @.nvcvalue = @.NotThisAppFilter
> EXEC sp_trace_setfilter @.queue_handle = @.queue_handle
> , @.columnid = 10 --column_id
> , @.logical_operator = 0--logical_operator
> , @.comparison_operator = 7--comparison_operator
> , @.value = @.nvcvalue--value
> BREAK
> END
> SELECT @.nvcvalue = SUBSTRING(@.NotThisAppFilter,1,@.cnt-1)
> SELECT @.NotThisAppFilter =
> SUBSTRING(@.NotThisAppFilter,@.cnt+1,LEN(@.
NotThisAppFilter)-@.cnt+1)
> EXEC sp_trace_setfilter @.queue_handle = @.queue_handle
> , @.columnid = 10 --column_id
> , @.logical_operator = 0--logical_operator
> , @.comparison_operator = 7--comparison_operator
> , @.value = @.nvcvalue--value
> END
>
> SELECT @.nvcvalue = @.queue_handle,@.vc=@.MinDurationFilter
> EXEC('DECLARE @.bi bigint
> SELECT @.bi = '+@.vc+'
> EXEC sp_trace_setfilter @.queue_handle = '+@.nvcvalue+'
> , @.columnid = 13 --column_id
> , @.logical_operator = 0--logical_operator
> , @.comparison_operator = 4--comparison_operator
> , @.value = @.bi')
> -- to start it
> EXEC sp_trace_setstatus @.queue_handle,1
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
> GO
> -- Script generated on 5/27/02 12:48 PM
> -- By: \Administrator
> -- Server: DATABASE1
> BEGIN TRANSACTION
> DECLARE @.JobID BINARY(16)
> DECLARE @.ReturnCode INT
> SELECT @.ReturnCode = 0
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Trace
> Monitoring') < 1
> EXECUTE msdb.dbo.sp_add_category @.name = N'Trace Monitoring'
> -- Delete the job with the same name (if it exists)
> SELECT @.JobID = job_id
> FROM msdb.dbo.sysjobs
> WHERE (name = N'Trace - Trace File Generation')
> IF (@.JobID IS NOT NULL)
> BEGIN
> -- Check if the job is a multi-server job
> IF (EXISTS (SELECT *
> FROM msdb.dbo.sysjobservers
> WHERE (job_id = @.JobID) AND (server_id <> 0)))
> BEGIN
> -- There is, so abort the script
> RAISERROR (N'Unable to import job ''Trace - Trace File Generation''
> since there is already a multi-server job with this name.', 16, 1)
> GOTO QuitWithRollback
> END
> ELSE
> -- Delete the [local] job
> EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Trace - Trace File
> Generation'
> SELECT @.JobID = NULL
> END
> BEGIN
> -- Add the job
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
> @.job_name = N'Trace - Trace File Generation', @.owner_login_name = N'sa',
> @.description = N'No description available.', @.category_name = N'Trace
> Monitoring', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0
,
> @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> -- Add the job steps
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id
=
> 1, @.step_name = N'Step 1', @.command = N'EXEC master.dbo.sp__TraceQuery
> @.FilePath = ''C:\SQLSERVERINFO'',@.NotThisAppFilter = ''SQL Server
> Profiler%;MS SQLEM%;SQL Query Analyzer%'',@.MinDurationFilter = 5000,@.StopI
t =
> 0', @.database_name = N'master', @.server = N'', @.database_user_name = N'',
> @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 0, @.retry_attemp
ts
> = 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0
,
> @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
> @.start_step_id = 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> -- Add the job schedules
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.nam
e
> = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 200205
13,
> @.active_start_time = 0, @.freq_interval = 1, @.freq_subday_type = 8,
> @.freq_subday_interval = 1, @.freq_relative_interval = 0,
> @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time
=
> 235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> -- Add the Target Servers
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
> @.server_name = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> END
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:
> GO
> "Bill Papanestor" wrote:
>