Microsoft KB Archive/286191

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

Article ID: 286191

Article Last Modified on 12/13/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q286191



SUMMARY
This article describes the ActivityTrace7 job that puts blocking and performance data into a trace file for further analysis. Copy the ActivityTrace7 job script from this article into a Query Analyzer window, and then 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.



MORE INFORMATION
The first time that the job is executed, it creates a file named C:\ActivityTrace7.ini that controls the ActivityTrace7 job. You can edit the ActivityTrace7.ini file by using Notepad.

To start the trace, edit the ActivityTrace7.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 starts the trace.

To stop the trace, edit the ActivityTrace7.ini file by changing both of the following variables as noted:
 * @blockingcheck = no @performancecheck = no

The next time that the job executes the trace stops.

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

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 that is gathered by the trace, click the article numbers below to view the articles in the Microsoft Knowledge Base:

289274 INF: How to View SQL Server 7.0 Activity Data

289276 INF: How to View SQL Server 7.0 Blocking Data

289277 INF: How to View SQL Server 7.0 Performance Data

For additional information about controlling a trace, click the article number below to view the article in the Microsoft Knowledge Base:

289279 INF: How to Monitor SQL Server 7.0 Traces

NOTE: Make sure that you stop the trace by changing the @blockingcheck and @performancecheck parameters to NO in the ActivityTrace7.ini file. If you stop the trace by means of the trace procedure described in the article just referenced (Q289279), the trace stops, but the job continues. The next time the job runs, it reads that at least one of the parameters in the ActivityTrace7.ini file is still set to YES and restarts the trace, overwriting the original ActivityTrace7.trc file.

The ActivityTrace7 job is controlled through the C:\ActivityTrace7.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 &quot;no block&quot; or &quot;blocking.&quot; If blocking exits, 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.
 * @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.
 * @events

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

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

When set to YES, the job ActivityTrace7 is scheduled to run every minute on the minute as defined by the schedule named schedule00seconds.
 * @schedule15seconds

When set to YES, the job ActivityTrace7 is 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 ActivityTrace7 is 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 ActivityTrace7 is 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 ActivityTrace7.ini file; hence, the job must be started manually to allow it to read the ActivityTrace7.ini file for the schedule change.

For additional information about the @events arguments, see the &quot;xp_trace_eventclassrequired (T-SQL)&quot; topic in SQL Server 7.0 Books Online.

For example, consider the following contents of an @traceini file named C:\ActivityTrace7.ini: @performancecheck = no @blockingcheck     = no @tracefile         = C:\ActivityTrace7 @minMBfree        = 200 @stoptime         = 2010-12-31 12:00:00.000 @events           = 10,11,12,13,16,17,19,33,42,43,55,82,83 @job_name         = ActivityTrace7 @schedule00seconds = yes @schedule15seconds = no @schedule30seconds = no @schedule45seconds = no By setting the variables this way in the ActivityTrace7.ini file, the job ActivityTrace7 (@jobname) runs every minute on the minute (@schedule00seconds = yes). This job is set up to create a trace file, C:\ActivityTrace7.trc (@tracefile), to contain blocking and performance trace activity. However, the trace is not started (@performancecheck = no, @blockingcheck = no). If the trace is started, the following occurs:

The following events are traced (@events):
 * RPC:Completed
 * RPC:Starting
 * SQL:BatchCompleted
 * SQL:BatchStarting
 * Attention
 * ExistingConnection
 * DTCTransaction
 * Exception
 * SP:Starting
 * SP:Completed
 * Hash Warning
 * UserConfigurable:1
 * UserConfigurable:2

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

ActivityTrace7 job script
-- Script generated on 2/19/2001 5:22 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'ActivityTrace7') > 0 PRINT N'The job &quot;ActivityTrace7&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'ActivityTrace7', @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:\ActivityTrace7.ini file declare @c nvarchar(256), @rc int, @i char(21), @on bit, @job_name sysname, @s sysname set @i = C:\ActivityTrace7.ini set @c = copy +@i+ C:\ActivityTrace7.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:\ActivityTrace7 >> +@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 @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 @job_name         = ActivityTrace7 >> +@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 + ') if exists (select * from dbo.sysobjects where id = object_id(N[_t2])  and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_t2] create table _t2 ([d1] nchar(1), [on] bit, [s1] nvarchar(128), [o1] nvarchar(245)) if exists (select * from dbo.sysobjects where id = object_id(N[_s])  and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_s] create table _s (id1 int identity, spid smallint, b smallint, d1 bit, ud binary(2540)) 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', @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 7 declare @p varchar(3), @b varchar(3), @tid int, @tfile nvarchar (245), @stop datetime , @m1 int, @rc int, @c nvarchar(128), @m2 int, @j sysname , @ets varchar(512), @e int, @es int, @en int, @le int select @b=cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = @pe select @p=cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (3)) from _t1 where left(c1,3) = @bl select @tfile=cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where left(c1,3) = @tr select @m1=cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as int) from _t1 where left(c1,3) = @mi select @stop=cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as datetime) from _t1 where left(c1,3) = @st select @j=cast(rtrim(ltrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as sysname) from _t1 where left(c1,3) = @jo select @ets=cast(ltrim(rtrim(substring(c1,charindex(=,c1,1)+1,len(c1)))) as nvarchar (245)) from _t1 where left(c1,3) = N@ev set @tid = 0 set @c = dir  + left(@tfile,2) +  | find &quot;bytes free&quot; insert into _t1 exec master.dbo.xp_cmdshell @c select @m2 = cast(replace(substring(c1,charindex(Dir,c1)+6,charindex(bytes free,c1)      -(charindex(Dir,c1)+6)),,,) as int)/1024/1024 from _t1 where charindex(bytes free,c1) > 0 delete _t1 where left([c1],1) != @ insert into _t1 EXEC master.dbo.xp_trace_enumqueuehandles while (select top 1 count(*) from _t1 where left(c1,1) != @) > 0 begin select top 1 @tid = cast(c1 as int) from _t1 where left(c1,1) != @ insert _t2 exec master.dbo.xp_trace_getqueuedestination @tid,2 if (select o1 from _t2) = @tfile + .trc delete _t1 where left(c1,1) != @ else delete from _t1 where c1 = cast(@tid as nvarchar(3)) and left(c1,1) != @ end if upper(@p) != YES and upper(@b) != YES goto stopt if @stop < getdate goto stopt if @m2 <= @m1 goto disable if @tid != 0 and @m2 > @m1 goto finish if @tid != 0 goto finish set @c = if exist +@tfile+.trc +del +@tfile+.trc exec @rc = master.dbo.xp_cmdshell @c, no_output if (@rc != 0) goto disable EXEC master.dbo.xp_trace_addnewqueue 3000, 6000, 95, 90, 129761279, @tid OUTPUT if (@rc != 0) goto disable select @es = 1 select @en = charindex(,,@ets,@es) set @le = len(@ets) while @en > 0 begin select @e = cast(substring(@ets,@es,@en-@es) as int) EXEC master.dbo.xp_trace_seteventclassrequired @tid, @e, 1 select @es = @en + 1 select @en = charindex(,,@ets,@es) if @en = 0 set @en = @le + 1 if @es > @le set @en = 0 end set @tfile = @tfile + .trc EXEC master.dbo.xp_trace_setqueuedestination @tid, 2, 1, NULL,@tfile EXEC master.dbo.xp_trace_startconsumer @tid goto finish disable: exec msdb.dbo.sp_update_job @job_name = @j, @enabled = 0 stopt: if @tid > 0 EXEC master.dbo.xp_trace_destroyqueue @tid 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'blocking', @command = N'-- blocking check declare @ud varbinary(2400),@id int,@spid smallint,@c nvarchar(80),@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(4))+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(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)) 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 master.dbo.xp_trace_generate_event @event_class = 82, @event_text_data = no 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 master.dbo.xp_trace_generate_event @event_class = 82, @event_text_data = @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 master.dbo.xp_trace_generate_event @event_class = 82, @event_text_data = blocking , @binary_data = @ud update _s set d1 = 1 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 = 4, @step_name = N'performance', @command = N'-- performance check declare @UserData nvarchar(128), @id1 int, @p varchar(3) 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 truncate table _t1 insert into _t1 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 count(*) from _t1) > 0 begin select top 1 @UserData = c1 from _t1 EXEC master.dbo.xp_trace_generate_event @event_class = 83, @event_text_data = @UserData delete _t1 where left(c1,40) + substring(c1,41,40) + substring(c1,82,35) = left(@UserData,40) + substring(@UserData,41,40) + substring(@UserData,82,35) end finish: if exists (select * from dbo.sysobjects where id = object_id(N[_t1]) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_t1] if exists (select * from dbo.sysobjects where id = object_id(N[_t2]) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_t2] if exists (select * from dbo.sysobjects where id = object_id(N[_s]) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table [_s]', @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 = 0, @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 = 0, @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 = 0, @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 KB286191

-

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

© Microsoft Corporation. All rights reserved.