Microsoft KB Archive/283696

= INF: Job to Monitor SQL Server 2000 Performance and Activity =

Article ID: 283696

Article Last Modified on 9/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q283696



SUMMARY
This article describes the ActivityTrace job that puts blocking and performance data into a trace file for further analysis. Copy the ActivityTrace job script from this article into a Query Analyzer window and execute the script to create the job. As is, the job is enabled and scheduled to run every minute.

'NOTE:' This article discusses a DBCC command (DBCC PSS) that is unsupported, and may cause unexpected behavior. Microsoft cannot guarantee that you can solve problems that result from the incorrect use of this DBCC command. Use this DBCC command at your own risk. This DBCC command may not be available in future versions of SQL Server. For a list of the supported DBCC commands, see the &quot;DBCC&quot; topic in the Transact-SQL Reference section of SQL Server Books Online.

The first time the job is executed, it creates a file named C:\ActivityTrace.ini that controls the ActivityTrace job. This file can be edited using Notepad.

To start the trace, edit the ActivityTrace.ini file by changing either or both of the following variables as noted: @blockingcheck = yes @performancecheck = yes After the change is made, the next execution of the job will start the trace.

To stop the trace, edit the ActivityTrace.ini file by changing both of the following variables as noted: @blockingcheck = no @performancecheck = no The next time the job executes the trace will stop.

The results of the trace are written to the C:\ActivityTrace.trc file. When the trace starts the original C:\ActivityTrace.trc file is overwritten, so be sure to copy the C:\ActivityTrace.trc file to another location before you restart the trace again.

Use SQL Profiler to view the data in the trace file to find blocking or a performance barrier.

For additional information about analyzing the data gathered by the trace, click the article numbers below to view the articles in the Microsoft Knowledge Base:

283784 INF: How to View SQL Server 2000 Activity Data

283725 INF: How to View SQL Server 2000 Blocking Data

283886 HOW TO: View SQL Server 2000 Performance Data

For information about controlling a trace, see the following article:

283786 INF: How to Monitor SQL Server 2000 Traces

NOTE: Make sure that you stop the trace by changing the @blockingcheck and @performancecheck parameters to no in the ActivityTrace.ini file. If you stop the trace by means of the trace stored procedure described in the article just referenced (Q283786), the trace will stop, but the job will continue. The next time the job runs, it will read that at least one of the parameters in the ActivityTrace.ini file is still set to yes and restart the trace, overwriting the original ActivityTrace.trc file.



MORE INFORMATION
The ActivityTrace job is controlled through the C:\ActivityTrace.ini file, which contains the following variables:
 * @performancecheck

If @performance is yes, user event 83 is used to write the sysperfinfo table to the trace file. If @performance is no, user event 83 is not generated.
 * @blockingcheck

If @blockingcheck is yes, user event 82 is used to record either 'no block' or 'blocking'. If blocking exists, user event 82 is used to record the sysprocesses and syslockinfo tables to the trace file. The user event 82 is also used to put dbcc pss information into the trace file if blocking exists. If @blockingcheck is no, that step is skipped and user event 82 is not generated.
 * @tracefile

Data is written to the trace file specified in this @tracefile variable. Change @tracefile to save the trace to a different drive and file name. Note that a .trc extension is automatically added by the trace.
 * @maxfilesize

When the trace file reaches the number of megabytes specified in the @maxfilesize variable, the file is closed. Change @maxfilesize to increase or reduce the size in megabytes of the trace file.
 * @minMBfree

When the megabytes of free space on the drive specified in @tracefile is less than the megabytes specified in @minMBfree, the trace is stopped.
 * @stoptime

Change @stoptime to set a time for the trace to automatically stop.
 * @options

When the @options argument is set to 2, the trace will create new trace files when the @maxfilesize variable is exceeded. When the trace file reaches the number of megabytes specified in the @maxfilesize variable, the file is closed and a new file is created. If the original trace file specified in the @tracefile variable is C:\ActivityTrace, the original file is C:\ActivityTrace.trc. The next trace file is C:\ActivityTrace_1.trc, the next trace file is C:\ActivityTrace_2.trc, and so on.
 * @events

Change @events to control the events traced.
 * @columns

Change @columns to control the columns traced.
 * @filter1

The number of @filter variables can vary from no @filter variable to @filter99. The filter variables must start with @filter1 and continue in sequence to the last filter. The four arguments in the @filter variable are column_id, logical_operator, comparison_operator, and value. The arguments are used by the system stored procedure, sp_trace_setfilter, to create a filter.
 * @job_name

Change the @job_name variable to alter the name of the trace job from ActivityTrace to some other job name. Note that if you change the ActivityTrace job name, you must also change the name in the ActivityTrace job properties.
 * @schedule00seconds

When set to yes, the job ActivityTrace will be scheduled to run every minute on the minute as defined by the schedule named schedule00seconds.
 * @schedule15seconds

When set to yes, the job ActivityTrace will be scheduled to run every minute at 15 seconds past the minute as defined by the schedule named schedule15seconds.
 * @schedule30seconds

When set to yes, the job ActivityTrace will be scheduled to run every minute at 30 seconds past the minute as defined by the schedule named schedule30seconds.
 * @schedule45seconds

When set to yes, the job ActivityTrace will be scheduled to run every minute at 45 seconds past the minute as defined by the schedule named schedule45seconds.

Note that if @schedule00seconds, @schedule15seconds, @schedule30seconds, and @schedule45seconds are all set to yes, the job runs every 15 seconds. If @schedule00seconds, @schedule15seconds, @schedule30seconds, and @schedule45seconds are all set to no, the job does not run at all. In this case, because the job is not running, it cannot detect a change to the ActivityTrace.ini file; hence, the job must be started manually to allow it to read the ActivityTrace.ini file for the schedule change.

For additional information about the @tracefile, @maxfilesize, @stoptime, and @options arguments, see the SQL Server 2000 Books Online article sp_trace_create.

For additional information about the @events and @columns arguments, see the SQL Server 2000 Books Online article sp_trace_setevent.

For more information about filters, see the SQL Server 2000 Books Online article sp_trace_setfilter.

For example, consider the following contents of a @traceini file called C:\ActivityTrace.ini: @performancecheck = no @blockingcheck     = no @tracefile         = C:\ActivityTrace @maxfilesize      = 50 @minMBfree        = 200 @stoptime         = 2010-12-31 12:00:00.000 @options          = 2 @events           = 10,11,12,13,16,17,19,33,42,43,55,82,83 @columns          = 1,2,3,6,9,10,11,12,13,14,15,16,17,18,25 @filter1          = 10, 0, 7, N'SQL Profiler' @filter2          = 10, 0, 7, N'SQLAgent - Job Manager' @filter3          = 10, 0, 7, N'SQLAgent - Alert Engine' @filter4          = 10, 0, 7, N'SQLAgent - Generic Refresher' @filter5          = 3, 0, 1, 4 @job_name         = ActivityTrace @schedule00seconds = yes @schedule15seconds = no @schedule30seconds = no @schedule45seconds = no By setting the variables this way in the ActivityTrace.ini file, the job ActivityTrace (@jobname) will run every minute on the minute (@schedule00seconds = yes). This job is set up to create a trace file, C:\ActivityTrace.trc (@tracefile), to contain blocking and performance trace activity; however, the trace will not be started (@performancecheck = no, @blockingcheck = no).

If the trace is started, when the tracefile reaches 50 MB (@maxfilesize), C:\ActivityTrace.trc will be closed and a new file, C:\ActivityTrace_1.trc will be created (@options). The following events will be traced (@events):
 * RPC:Completed
 * RPC:Starting
 * SQL:BatchCompleted
 * SQL:BatchStarting
 * Attention
 * ExistingConnection
 * DTCTransaction
 * Exception
 * SP:Starting
 * SP:Completed
 * Hash Warning
 * User-defined event 82
 * User-defined event 83

The following columns will be traced (@columns):
 * TextData
 * BinaryData
 * DatabaseID
 * NTUserName
 * ClientProcessID
 * ApplicationName
 * SQLSecurityLoginName
 * SPID
 * Duration
 * StartTime
 * EndTime
 * Reads
 * Writes
 * CPU
 * IntegerData

The trace will filter events so that it only captures events where ApplicationName NOT LIKE 'SQL Profiler' (@filter1), ApplicationName NOT LIKE 'SQLAgent - Job Manager' (@filter2), ApplicationName NOT LIKE 'SQLAgent - Alert Engine' (@filter3), ApplicationName NOT LIKE 'SQLAgent - Generic Refresher' (@filter4), and DatabaseID NOT EQUAL 4 (@filter5).

The trace will stop at 12:00pm on Dec. 31, 2010 (@stoptime).

ActivityTrace job script
-- Script generated on 2/9/2001 12:02 PM BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'ActivityTrace') > 0 PRINT N'The job &quot;ActivityTrace&quot; already exists so will not be replaced.' ELSE BEGIN

-- Add the job. EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'ActivityTrace', @owner_login_name = N'sa', @description = N'Trace performance and blocking.', @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'CreateIniFile', @command = N'-- create C:\ActivityTrace.ini file declare @c nvarchar(256), @rc int, @i char(20) set @i = C:\ActivityTrace.ini set @c = copy C:\ActivityTrace.ini C:\ActivityTrace.bak exec @rc = master.dbo.xp_cmdshell @c, no_output if (@rc = 0) goto finish set @c = echo @blockingcheck    = no > +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @performancecheck = no >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @tracefile        = C:\ActivityTrace >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @maxfilesize      = 50 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @minMBfree        = 200 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @stoptime         = 2010-12-31 12:00:00.000 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @options          = 2 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @events           = 10,11,12,13,16,17,19,33,42,43,55,82,83 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @columns          = 1,2,3,6,9,10,11,12,13,14,15,16,17,18,25 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @filter1          = 10, 0, 7, N'SQL Profiler' >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @filter2          = 10, 0, 7, N'SQLAgent - Job Manager' >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @filter3          = 10, 0, 7, N'SQLAgent - Alert Engine' >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @filter4          = 10, 0, 7, N'SQLAgent - Generic Refresher' >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @filter5          = 3, 0, 1, 4 >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @job_name         = ActivityTrace >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @schedule00seconds = yes >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @schedule15seconds = no >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @schedule30seconds = no >> +@i exec master.dbo.xp_cmdshell @c, no_output set @c = echo @schedule45seconds = no >> +@i exec master.dbo.xp_cmdshell @c, no_output finish: if exists (select * from dbo.sysobjects where id = object_id(N[_t1]) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_t1] create table _t1 ([c1] nvarchar(512)) exec (bulk insert _t1 FROM '+@i + ') ', @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 = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'CreateTrace', @command = N'-- create trace declare @p varchar(3), @b varchar(3), @traceid int, @options int, @tracefile nvarchar (245) , @maxfilesize bigint, @stoptime datetime, @minMBfree bigint, @rc int, @on bit , @cmd1 nvarchar(128), @mbfree bigint, @job_name sysname, @s sysname select @p = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = @pe select @b = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = @bl select @tracefile = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where left(c1,3) = @tr select @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as bigint) from _t1 where left(c1,3) = @ma select @minMBfree = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as bigint) from _t1 where left(c1,3) = @mi select @stoptime = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as datetime) from _t1 where left(c1,3) = @st select @options = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as int) from _t1 where left(c1,3) = @op select @job_name = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as sysname) from _t1 where left(c1,3) = @jo while (select count(*) from _t1 where left(c1,3) = @sc) > 0 begin select top 1 @s = cast(rtrim(ltrim(substring(c1,2,charindex(=,c1,1)-2))) as sysname) , @on = case upper(cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as varchar(3))) when YES then 1 else 0 end from _t1 where left(c1,3) = @sc EXEC msdb.dbo.sp_update_jobschedule @job_name=@job_name, @name = @s, @enabled = @on delete _t1 where cast(rtrim(ltrim(substring(c1,2,charindex(=,c1,1)-2))) as sysname) = @s end set @on = 1 set @traceid = 0 if @stoptime < getdate goto stoptrace set @cmd1 = dir  + left(@tracefile,2) +  | find &quot;bytes free&quot; insert into _t1 exec master.dbo.xp_cmdshell @cmd1 select @mbfree = cast(replace(substring(c1,charindex(Dir,c1)+6,charindex(bytes free,c1)      -(charindex(Dir,c1)+6)),,,) as bigint)/1024/1024 from _t1 where charindex(bytes free,c1) > 0 delete _t1 where left([c1],1) != @ SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile if upper(@p) != YES and upper(@b) != YES goto stoptrace if @traceid != 0 and @mbfree > @minMBfree goto finish if @mbfree <= @minMBfree goto disable if @traceid != 0 goto finish set @cmd1 = if exist  + @tracefile + .trc  + del  + @tracefile + *.trc exec @rc = master.dbo.xp_cmdshell @cmd1, no_output if (@rc != 0) goto disable exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime if (@rc != 0) goto disable goto finish disable: exec msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 0 stoptrace: exec sp_trace_setstatus @traceid, 0 exec sp_trace_setstatus @traceid, 2 finish:', @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 = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'SetEvents', @command = N'-- set trace events and filters declare @traceid int, @tracefile nvarchar (245), @rc int, @on bit, @cmd1 nvarchar(256) , @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int , @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int set @on = 1 set @traceid = 0 select @tracefile = cast(ltrim(rtrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where left(c1,3) = N@tr select @events=cast(ltrim(rtrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (512)) from _t1 where left(c1,3) = N@ev select @columns=cast(ltrim(rtrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (512)) from _t1 where left(c1,3) = N@co SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile if @traceid = 0 goto finish if (SELECT count(*) FROM ::fn_trace_geteventinfo(@traceid)) > 0 goto finish select @estart = 1 select @enext = charindex(,,@events,@estart) select @cstart = 1 select @cnext = charindex(,,@columns,@cstart) set @le = len(@events) set @lc = len(@columns) while @enext > 0 begin select @event = cast(substring(@events,@estart,@enext-@estart) as int) while @cnext > 0 begin select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int) exec sp_trace_setevent @traceid, @event, @column, @on select @cstart = @cnext + 1 select @cnext = charindex(,,@columns,@cstart) if @cnext = 0 set @cnext = @lc + 1 if @cstart >@lc set @cnext = 0 end select @cstart = 1 select @cnext = charindex(,,@columns,@cstart) select @estart = @enext + 1 select @enext = charindex(,,@events,@estart) if @enext = 0 set @enext = @le + 1 if @estart > @le set @enext = 0 end set @cmd1 = exec sp_trace_setfilter  set @filter = Nnone select @filter = cast(ltrim(rtrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where cast(ltrim(rtrim(substring(c1,1,charindex(=,c1,1)-1))) as nvarchar (245)) = N@filter1 set @filter_num = 2 while @filter != Nnone begin exec (@cmd1 + @traceid + ,+@filter) set @filter_num = @filter_num + 1 set @filter = Nnone select @filter = cast(ltrim(rtrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where cast(ltrim(rtrim(substring(c1,1,charindex(=,c1,1)-1))) as nvarchar (245)) = N@filter + cast(@filter_num as nvarchar(3)) select @filter end finish: exec sp_trace_setstatus @traceid, 1', @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 = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'blocking', @command = N'-- blocking check declare @s table(id1 int identity, spid smallint, b smallint, d1 bit, ud binary(2540)) declare @ud binary(2540), @id int, @spid smallint, @c nvarchar(128), @b varchar(3), @rc int,@ui nvarchar(128) select @b = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = @bl if upper(@b) != YES goto finish insert into @s select spid, blocked, 0 ,cast(spid as binary(2))+cast(kpid as binary(2))+cast(blocked as binary(2))+waittype +cast(waittime as binary(4))+cast(lastwaittype as binary(64))+cast(waitresource as binary(512)) +cast(dbid as binary(2))+cast(uid as binary(2))+cast(cpu as binary(4)) +cast(physical_io as binary(8))+cast([memusage] as binary(4))+cast(login_time as binary(8)) +cast(last_batch as binary(8)) +cast(ecid as binary(2))+cast(open_tran as binary(2)) +cast(status as binary(60))+cast(sid as binary(86))+cast(hostname as binary(256)) +cast(program_name as binary(256))+cast(hostprocess as binary(16))+cast(cmd as binary(32)) +cast(nt_domain as binary(256))+cast(nt_username as binary(256))+cast(net_address as binary(24)) +cast(net_library as binary(24))+cast(loginame as binary(256))+cast([context_info] as binary(128)) +cast(l.rsc_text as binary(64))+cast(rsc_bin as binary(16))+cast(rsc_valblk as binary(16)) +cast(rsc_dbid as binary(2))+cast(rsc_indid as binary(2))+cast(rsc_objid as binary(4)) +cast(rsc_type as binary(1))+cast(rsc_flag as binary(1)) +cast(req_mode as binary(1)) +cast(req_status as binary(1))+cast(req_refcnt as binary(2))+cast(req_cryrefcnt as binary(2)) +cast(req_lifetime as binary(4))+cast(req_spid as binary(4))+cast(req_ecid as binary(4)) +cast(req_ownertype as binary(2))+cast(req_transactionID as binary(8)) +cast(req_transactionUOW as binary(16)) from master.dbo.sysprocesses p join master.dbo.syslockinfo l on p.spid = l.req_spid where (blocked != 0 or waittype != 0x0000) or (blocked = 0 and spid in (select blocked from master.dbo.sysprocesses where blocked != 0)) if (select top 1 count(*) from @s) < 1 exec sp_trace_generateevent 82,  Nno block else begin update @s set d1 = 1 where b = 0 and spid in (select b from @s where b != 0) while (select top 1 count(*) from @s where d1 = 1) > 0 begin select top 1 @spid = spid from @s where d1 = 1 set @c = osql -S+@@servername+ -Q&quot;dbcc traceon(3604) dbcc pss(0,        + cast(@spid as nvarchar(3))+ )&quot; -o C:\pss.txt -w128 exec @rc = master.dbo.xp_cmdshell @c, no_output if (@rc = 0) bulk insert _t1 FROM C:\pss.txt delete _t1 where left([c1],2) = 00 update @s set d1 = 0 where spid = @spid while (select top 1 count(*) from _t1 where left([c1],1) != @) > 0 begin select top 1 @ui = c1 from _t1 where left([c1],1) != @ exec sp_trace_generateevent 82, @ui delete _t1 where left(c1,5) = left(@ui,5) end set @c = del C:\pss.txt exec master.dbo.xp_cmdshell @c, no_output end while (select top 1 count(*) from @s where d1 = 0) > 0 begin select top 1 @id = id1, @ud = ud from @s where d1 = 0 exec sp_trace_generateevent 82, Nblocking, @ud delete @s where id1 = @id end end finish:', @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 = 3, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 5, @step_name = N'performance', @command = N'-- performance check declare @s table (id1 int IDENTITY, UserData nvarchar(128)) declare @UserData nvarchar(128), @id1 int, @p varchar(3), @b varchar(3), @rc int , @cmd nvarchar(80),@userinfo nvarchar(128), @row int, @lastrow int select @p = cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = @pe if upper(@p) != YES goto finish insert into @s select left([object_name],40) + left([counter_name],40) + left([instance_name],35) + cast([cntr_value] as nchar(11)) from master.dbo.sysperfinfo where cntr_value > 0 while (select top 1 [id1] from @s) > 0 begin select top 1 @id1 = [id1], @UserData = [UserData] from @s exec sp_trace_generateevent @eventid = 83, @userinfo = @UserData delete @s where [id1] = @id1 end finish: if exists (select * from dbo.sysobjects where id = object_id(N[_t1]) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_t1]', @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'schedule00seconds', @enabled = 1, @freq_type = 4, @active_start_date = 20001220, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @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 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule30seconds', @enabled = 1, @freq_type = 4, @active_start_date = 20001227, @active_start_time = 30, @freq_interval = 1, @freq_subday_type = 4, @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 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule15seconds', @enabled = 1, @freq_type = 4, @active_start_date = 20001227, @active_start_time = 15, @freq_interval = 1, @freq_subday_type = 4, @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 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'schedule45seconds', @enabled = 1, @freq_type = 4, @active_start_date = 20001227, @active_start_time = 45, @freq_interval = 1, @freq_subday_type = 4, @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:

Keywords: kbinfo KB283696

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.