Understanding the impact of SQLTrace on production Server is very important because we need to keep few traces running all the time to answer customer questions about what was wrong on server whenever client complain about performance.
The Profiler Trace will always impact server performance. The act of monitoring requires some resources. The key is to know don't have a habit to leave extra Traces running and trap everything on production server.
To track client reported issues sometimes Developers\ DBAs run SQL Server Profiler Trace on production server and leave them unstopped.
The script schedule a job which run every 30 minutes to stop the redundant traces on server and if any trace is been stopped it generate email using Database Email.
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- To Stop Unnecessary User Traces With Execution Time > Half Hour
USE [msdb]
GO
/****** Object: Job [StopUserRedundantTraces] Script Date: 05/28/2015 09:39:33 ******/
BEGINTRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/28/2015 09:39:33 ******/
IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]'AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category
@class=N'JOB'
, @type=N'LOCAL'
, @name=N'[Uncategorized (Local)]'
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'StopUserRedundantTraces',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'LoginUserName',-- Change values
@notify_email_operator_name=N'EmailNotifyOperator' -- Change values
, @job_id = @jobId OUTPUT
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
/****** Object: Step [StopTrace] Script Date: 05/28/2015 09:39:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StopTrace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'/*
Script by: Amna Asif
Purpose: To Stop Unnecessary User Traces With Execution Time > Half Hour
*/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
DECLARE @TraceID NVARCHAR(255)
Declare @Body varchar(2000)
DECLARE ActiveTraceList CURSOR
FOR
SELECT tr.id
FROM sys.traces tr
WHERE status=1
AND is_rowset = 1
AND is_default<>1--and reader_spid is not null
AND ISNULL(path,'''') NOT LIKE ''%ServerDBATrace_%''-- Change values
AND DATEDIFF(mi,start_time,GETDATE()) >30
OPEN ActiveTraceList
FETCH NEXT FROM ActiveTraceList INTO @TraceID
WHILE @@FETCH_STATUS = 0
BEGIN
select @Body='''','''')
from sys.traces
where id=@TraceID
exec ('' sp_trace_setstatus ''+@TraceID+'', 0'')
exec ('' sp_trace_setstatus ''+@TraceID+'', 2'')
EXEC msdb.dbo.sp_send_dbmail
@recipients=''aa.sc@outlook.com'',
@subject = ''StopUserRedundantTraces'',
@profile_name = ''EmailProfile'',-- Change values
@body = @Body,
@body_format = ''HTML'' ;
FETCH NEXT FROM ActiveTraceList INTO @TraceID
END
CLOSE ActiveTraceList
DEALLOCATE ActiveTraceList
',
@database_name=N'master',
@flags=0
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Scheduled for 24 hours',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140130,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N'(local)'
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT> 0)ROLLBACK TRANSACTION
EndSave:
GO
