Automating SQL Profiler

Stored procedures to automate SQL profiler. Here is the two scripts which I use to automate running SQL profiler on a weekly basis.

This script sets the necessary settings for profiler.
Three things you must do before running this stored procedure.

1. You need to add a path to the file. I normally use d:\perfmon\database_... This script will add the date and time to the end of the filepath. This means the filename looks something like _20050914_150.trc
2. Check or change the maxfilesize. It is currently set to 50M.
   
3. Check or change the duration filter. It is currently set to only capture SQL/Stored Procedures which run for more than 500ms.
4. Change or remove the filter for database name. I normally trace the specific database related to the application.

Have Fun

--- script begins here ---

use msdb
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dba_trc_database]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dba_trc_database]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dba_trc_database

@Sec int = 7200 -- Duration in seconds for the trace (2 hours)
, @TraceID int OUTPUT -- Return the TraceID to the caller.
WITH RECOMPILE
/*
* Creates a trace on the selected database.
***********************************************************************/
AS
declare @rc int
declare @dbaTraceID int
declare @maxfilesize bigint
declare @DateTime datetime
DECLARE @FileName nvarchar(256)
, @StartDT datetime -- When the trace started

SELECT @StartDT = getdate()
SELECT @FileName = N'c:\trace_'+convert(varchar,getdate(),112)+'_'+cast(datepart(hh,getdate()) as varchar)+cast(datepart(mi,getdate()) as varchar) ,
@DateTime = DATEADD (s, @Sec, @StartDT)

set @maxfilesize = 50

exec @rc = sp_trace_create @TraceID output, 2, @FileName , @maxfilesize, @Datetime

if (@rc != 0) goto error

-- Set the events
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 8, @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, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 2, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Duration greater than 500ms
set @bigintfilter = 500
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

-- If you want a specific database traced change to database name
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'insert database name here'
-- Enable trace

exec sp_trace_setstatus 1, 1

-- display trace id for future references
print @TraceID
goto finish

error:
select ErrorCode=@rc

finish:

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--- script ends here ---

The second script is to setup a weekly trace at 3pm. It will run for 2 hours (7200 secs).
Steps before running this script

1. Check or modify the duration of the trace. The current duration is 7200 secs (2 hours)
2. Check or modify the time the job will run. This is easier to perform within Enterprise Manager after the job has been created.
   

--- script begins here ---

-- Script generated on 4/22/2005 11:06 AM
-- By: sa
-- Server:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') <>
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Weekly shoulder peak profiler trace')
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 ''Weekly shoulder peak profiler trace'' 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'Weekly shoulder peak profiler trace'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Weekly shoulder peak profiler trace', @owner_login_name = N'sa', @description = N'Runs a profiler trace to 50M or half hour on a shoulder peak (3pm)', @category_name = N'[Uncategorized (Local)]', @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'Trace database', @command = N'declare @traceID int
declare @rc int

exec dba_Trc_database ''7200'',@traceID

PRINT ''RC from starting Trace = '' + convert(varchar(9), @rc)
PRINT ''DBA TraceID = '' + convert(varchar(9), @traceID)', @database_name = N'msdb', @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'Weekly', @enabled = 1, @freq_type = 8, @active_start_date = 20050323, @active_start_time = 150000, @freq_interval = 2, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @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:

--- script ends here ---



Required Disclaimer: Use at your own risk. I accept NO responsibility for any losses which occur in the use of this script.

profiler
SQL