INF: 作业,以监视 SQL Server 2000 性能和活动

文章翻译 文章翻译
文章编号: 283696 - 查看本文应用于的产品
展开全部 | 关闭全部

概要

本文介绍了将阻止和 $ 性能数据放入一个跟踪文件中,以便进一步分析该 ActivityTrace 作业。将从本文中 ActivityTrace 作业脚本复制到一个查询分析器窗口,并执行脚本以创建该作业。因为是,作业是启用并计划运行每分钟。


NOTE: 本文讨论了 DBCC 命令 (DBCC PSS) 的不受支持,可能会导致意外的行为。Microsoft 不能保证可以解决所产生的错误地使用此 DBCC 命令的问题。使用此 DBCC 命令需要您自担风险。此 DBCC 命令可能不可用的 SQL Server 的未来版本中。支持的 DBCC 命令一资源列表请参阅"DBCC"主题的 SQL Server 联机丛书 TRANSACT-SQL 参考部分中。

第一次执行作业时,它创建一个名为 C:\ActivityTrace.ini 控制 ActivityTrace 作业的文件。可以使用记事本编辑此文件。

若要开始跟踪,编辑 ActivityTrace.ini 文件,通过更改以下变量所述的一项或两项:
@blockingcheck = yes
@performancecheck = yes
将进行更改之后,该作业的下一次执行将开始跟踪。

若要停止该跟踪,编辑 ActivityTrace.ini 文件通过更改以下变量所述的两个:
@blockingcheck = no
@performancecheck = no
作业执行跟踪的下一个时间将停止。

跟踪的结果将被写入到 C:\ActivityTrace.trc 文件中。 跟踪开始时覆盖原始 C:\ActivityTrace.trc 文件,因此请务必之前再次重新启动该跟踪将 C:\ActivityTrace.trc 文件复制到另一个位置。

使用 SQL 事件探查器跟踪文件以查找阻塞或性能障碍中查看数据。

关于分析收集到的跟踪数据的其他信息请单击下面文章编号,以查看 Microsoft 知识库中相应的文章:
283784INF: 如何查看 SQL Server 2000 活动数据
283725INF: 如何查看 SQL Server 2000 阻止数据
283886如何: 查看 SQL Server 2000 性能数据
有关控制跟踪的信息,请参阅下面的文章:
283786INF: 如何监视 SQL Server 2000 跟踪
注:请确保您通过更改 @ blockingcheck 和 @ performancecheck 参数不在该 ActivityTrace.ini 文件停止跟踪。如果您停止,您可以 跟踪 的方式跟踪存储过程文章仅引用 (Q283786) 中所述,将在停止跟踪,但该作业将继续。下一次作业运行时,它将读取至少其中一个参数 ActivityTrace.ini 文件中仍然设置为是和重新启动该跟踪并覆盖原始 ActivityTrace.trc 文件。

更多信息

ActivityTrace 作业控制通过 C:\ActivityTrace.ini 文件包含以下变量:
  • @ performancecheck
    如果是肯定的 @ 性能用户事件 83 用于 sysperfinfo 表写入跟踪文件。如果 @ 性能不,用户事件 83 不会生成。
  • @ blockingcheck
    如果是肯定的 @ blockingcheck 用户事件 82 是用来记录或者块或阻塞。如果存在阻塞,用户事件 82 用于记录 sysprocesses 和 syslockinfo 表到跟踪文件。用户事件 82 还用于将放到跟踪文件中如果阻塞 pss 信息存在的 dbcc。如果 @ blockingcheck 是没有,跳过步骤,并不会生成用户事件 82。
  • @ tracefile
    在此 @ tracefile 变量中指定的跟踪文件写入数据。更改 @ tracefile 将跟踪保存到一个不同的驱动器和文件名称。 请注意跟踪可自动添加一个带.trc 扩展名的。
  • @ maxfilesize
    当跟踪文件达到 @ maxfilesize 变量中指定兆字节为单位的数目时,则文件关闭。更改 @ maxfilesize 以增加或减少以兆字节为单位跟踪文件的大小。
  • @ minMBfree
    在 $ @ tracefile 中指定的驱动器上的可用空间的兆字节小于该兆字节为单位,指定在 @ minMBfree,停止跟踪。
  • @ stoptime
    更改 @ stoptime 以设置要跟踪自动停止的时间。
  • @ 选项
    当在 @ options 参数设置为 2 时,该跟踪将创建新的跟踪文件,超过 @ maxfilesize 变量时。跟踪文件达到 @ maxfilesize 变量中指定兆字节为单位的数目时, 关闭文件,并创建一个新的文件。如果原始 @ tracefile 变量中指定的跟踪文件是 C:\ActivityTrace,原始文件是 C:\ActivityTrace.trc。下一个跟踪文件是 C:\ActivityTrace_1.trc 下, 一个跟踪文件是 C:\ActivityTrace_2.trc,等等。
  • @ 事件
    更改 @ 控制跟踪该事件的事件。
  • @ 列
    更改 @ 控制跟踪列的列。
  • @ filter1
    @ 筛选器变量数可以因 @ filter99 没有 @ 筛选器变量。筛选器变量必须以 @ filter1 开头,并继续在序列中最后一个筛选器。@ 筛选器变量中的四个参数是 column_id、 logical_operator、 comparison_operator,和值。若要创建一个筛选器,请使用系统存储过程的 sp_trace_setfilter,参数。
  • @ job_name
    更改该 @ job_name 变量,使其更改为其他作业名称从 ActivityTrace 跟踪作业的名称。请注意是否更改了 ActivityTrace 作业名称您还必须更改中 ActivityTrace 作业属性名称。
  • @ schedule00seconds
    设置为 yes 时,将分钟上运行每分钟,定义的名为 schedule00seconds 日程安排作业 ActivityTrace。
  • @ schedule15seconds
    设置为 yes 时,将在过去的分钟数的 15 秒运行每分钟,定义的名为 schedule15seconds 日程安排作业 ActivityTrace。
  • @ schedule30seconds
    设置为 yes 时,将在过去的分钟数的 30 秒运行每分钟,定义的名为 schedule30seconds 日程安排作业 ActivityTrace。
  • @ schedule45seconds
    设置为 yes 时,将在过去的分钟数的 45 秒运行每分钟,定义的名为 schedule45seconds 日程安排作业 ActivityTrace。
请注意是否 @ schedule00seconds、 @ schedule15seconds、 @ schedule30seconds 和 @ schedule45seconds 设置为是,作业都将运行每隔 15 秒。如果 @ schedule00seconds、 @ schedule15seconds、 @ schedule30seconds 和 @ schedule45seconds 都设置为否,不在所有运行作业。在这种情况下在作业未运行,因为它不能检测 ActivityTrace.ini 文件进行更改 ; 因此,作业必须手动启动以允许其读取 ActivityTrace.ini 文件以使日程安排更改。

有关 @ tracefile、 @ maxfilesize、 @ stoptime 和 @ 选项参数的其他信息,请参阅 SQL Server 2000 丛书联机文章 sp_trace_create

有关事件 @ 和 @ 列参数的其他信息,请参阅 SQL Server 2000 丛书联机文章 sp_trace_setevent

有关筛选器的详细信息请参阅 SQL Server 2000 丛书联机文章 sp_trace_setfilter

例如对于考虑称为 C:\ActivityTrace.ini @ traceini 文件中的以下内容:
@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
				
按 ActivityTrace.ini 文件中设置变量这种方式,ActivityTrace (@ jobname) 将要运行作业每分钟上一分钟 (@ schedule00seconds = 是)。此作业设置以创建一个跟踪文件,C:\ActivityTrace.trc (@ tracefile) 包含阻塞和性能跟踪活动 ; 但是,该跟踪将不会启动 (@ performancecheck = 否,@ blockingcheck = 否)。

如果跟踪已启动时该 tracefile 达到 50 MB (@ maxfilesize),C:\ActivityTrace.trc 都将被关闭和一个新文件 C:\ActivityTrace_1.trc 将会创建 (@ 的选项)。下列事件将为跟踪 (@ 事件):
  • RPC: 已完成
  • RPC: 正在启动
  • SQL:BatchCompleted
  • SQL:BatchStarting
  • 注意
  • ExistingConnection
  • DTCTransaction
  • 异常
  • SP: 正在启动
  • SP: 已完成
  • 哈希警告
  • 用户定义的事件 82
  • 用户定义的事件 83
下面的列将为跟踪 (@ 列):
  • TextData
  • BinaryData
  • DatabaseID
  • NTUserName
  • ClientProcessID
  • ApplicationName
  • SQLSecurityLoginName
  • SPID
  • 持续时间
  • 开始时间
  • 结束时间
  • 读取
  • 写操作
  • CPU
  • IntegerData
该跟踪将筛选事件,以便它仅捕获事件位置 (@ filter2) 不 ApplicationName LIKE SQL 事件探查器 (@ filter1) 不 ApplicationName LIKE SQLAgent-作业管理器不 ApplicationName LIKE SQLAgent-警报引擎 (@ filter3) 不 ApplicationName LIKE SQLAgent-通用的刷新器 (@ filter4) 和 DatabaseID 不等于 4 (@ filter5)。

该跟踪将停止 2010年 12 月 31日 (@ stoptime) 下午 12: 00。

ActivityTrace 作业脚本

-- 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 "ActivityTrace" 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, N''IsUserTable'') = 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 "bytes free"''
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 = N''none''
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 != N''none''
 begin
 exec (@cmd1 + @traceid + '',''+@filter)
 set @filter_num = @filter_num + 1
 set @filter = N''none''
 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,  N''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"dbcc traceon(3604) dbcc pss(0,''
         + cast(@spid as nvarchar(3))+ '')" -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, N''blocking'', @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, N''IsUserTable'') = 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: 
				

属性

文章编号: 283696 - 最后修改: 2003年9月16日 - 修订: 3.4
这篇文章中的信息适用于:
  • Microsoft SQL Server 2000 标准版
关键字:?
kbmt kbinfo KB283696 KbMtzh
机器翻译
注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。
点击这里察看该文章的英文版: 283696
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com