Automatically Stop Redundant Profiler Trace on Production Server

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

Change Column DataType

Datatype of column may require modification with the new change to the application. Column used in multiple tables could be dynamically changed in one execution using Alter Table command.

ALTER TABLE schemaName.tableName
ALTER COLUMN columnName DataType [NULL|NOT NULL]

The following example increases the size of a varchar\nvarchar column.

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com  
Use DBName
GO
SELECT 'ALTER TABLE [' +sc.name+'].['+object_name(col.object_id)

         +'] ALTER COLUMN ['+ col.name+'] '

+ CASE DATA_TYPE WHEN 'varchar' THEN DATA_TYPE +'(255) NULL ' 

                                  WHEN 'nvarchar' THEN DATA_TYPE +'(175) NULL '

   ELSE ''  END

,col.*

FROM sys.columns col

INNER JOIN sys.tables tb ON col.[object_id]=tb.[object_id]

INNER JOIN sys.schemas sc ON sc.[schema_id]=tb.[schema_id]

INNER JOIN INFORMATION_SCHEMA.COLUMNS incol ON incol.TABLE_NAME=tb.name

                                            AND incol.TABLE_SCHEMA=sc.name

                                            AND incol.COLUMN_NAME=col.name



WHERE DATA_TYPE in ('varchar','nvarchar') AND col.name ='CaseDetail'

AND CHARACTER_MAXIMUM_LENGTH<255

Note: 

1. Reducing the precision or scale of a column may cause data truncation. 
2. Column contain data, its new size should be equal to or greater than the old size. 
3. Read modified column cannot be any of on Link