INF: 工作來監視 SQL Server 2000 效能與活動

重要:本文是以 Microsoft 機器翻譯軟體翻譯而成,而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章,讓使用者可以依其使用語言使用知識庫中的所有文章。但是,機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤,就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。

按一下這裡查看此文章的英文版本:283696
本文已封存。本文係以「現狀」提供且不會再更新。
結論
本文將告訴您,會封鎖及效能資料放入追蹤檔案以進行進一步的分析 ActivityTrace 工作。將 ActivityTrace 作業指令碼從這份文件複製到 Query Analyzer 視窗並執行指令碼來建立工作。現狀,工作是啟用,排定執行每一分鐘。


NOTE: 本文將告訴您,DBCC 命令 (DBCC PSS),不支援,且可能會導致未預期的行為。Microsoft 不保證您可以解決問題所造成的此 DBCC 命令的使用不正確。使用此 DBCC] 指令,請自行負擔相關的風險。此 DBCC 命令可能無法在未來版本的 SQL Server 中使用。如需清單支援的 DBCC 命令請參閱 SQL Server 線上叢書 》 的 Transact-SQL 參考 > 一節中 DBCC 」 主題]。

第一次執行工作它建立一個名為 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 Profiler 檢視 [追蹤檔案以尋找封鎖或效能障礙中的 [資料。

取得更多資訊有關分析資料的追蹤所收集到按一下面的文件編號,檢視 「 Microsoft 知識庫 」 中 「 文件:
283784INF: 如何檢視 SQL Server 2000 活動資料
283725INF: 如何檢視 SQL Server 2000 封鎖資料
283886如何: 檢視 SQL Server 2000 的效能資料
如需有關控制追蹤資訊,請參閱下列文件:
283786INF: 如何監視 SQL Server 2000 的追蹤
注意:請確定您藉由變更至不在 [ActivityTrace.ini 檔案 @ blockingcheck 和 @ performancecheck 參數來停止追蹤。如果您停止透過 追蹤 的方式追蹤預存程序只需參考文章 (Q283786) 所述,追蹤將會停止,但工作將繼續。下一次工作執行時,將會標示至少一個 ActivityTrace.ini 檔案中的參數設定仍然為 [是],然後重新啟動覆寫原始檔案 ActivityTrace.trc 追蹤。
其他相關資訊
ActivityTrace 工作控制透過 C:\ActivityTrace.ini 檔案包含下列變數:
  • @ performancecheck
    如果 @ 效能是使用者事件 83 會使用追蹤檔中寫入 sysperfinfo 資料表。如果是 @ 效能不,使用者事件 83 就不會產生。
  • @ blockingcheck
    如果 @ blockingcheck 是使用者事件 82 是用來記錄任一個 '沒有區塊' 或 '封鎖'。如果封鎖存在使用者事件 82 用來記錄到追蹤檔的 [sysprocesses] 和 [syslockinfo] 資料表。使用者事件 82 也用來放置的 dbcc PSS 資訊到追蹤檔如果封鎖存在。如果是 @ blockingcheck 沒有,跳過步驟,且不會產生使用者事件 82。
  • @ tracefile
    資料會寫入此 @ tracefile 變數所指定的追蹤檔案。變更 @ tracefile 將追蹤儲存到不同的磁碟機和檔案名稱。 請注意副檔名為.trc 的檔案會自動加入追蹤所。
  • @ maxfilesize
    當追蹤檔案達到 @ maxfilesize 變數所指定的百萬位元組數目時,會關閉檔案。變更 @ maxfilesize 來增加或減少以百萬位元組 (MB) 為單位的追蹤檔案的大小。
  • @ minMBfree
    當 MB 的 @ tracefile 中指定磁碟機上的可用空間小於指定於 @ minMBfree MB,已停止追蹤。
  • @ stoptime
    變更 @ stoptime 若要設定追蹤自動停止的時間。
  • @ 選項
    當 @ 選項引數設定為 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
    當設定為 [是],ActivityTrace 工作將被排定執行每分鐘上分鐘所定義的具名 schedule00seconds 排程。
  • @ schedule15seconds
    當設定為 [是],工作 ActivityTrace 將被排定執行每分鐘在 15 秒過去的分鐘數所定義的具名 schedule15seconds 排程。
  • @ schedule30seconds
    當設定為 [是],工作 ActivityTrace 將被排定執行每分鐘在過去的分鐘 30 秒所定義的具名 schedule30seconds 排程。
  • @ schedule45seconds
    當設定為 [是],工作 ActivityTrace 將被排定執行每分鐘在 45 秒過去的分鐘數所定義的具名 schedule45seconds 排程。
請注意是否 @ 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
  • 例外狀況
  • 預存程序: 開始
  • 預存程序: 已完成
  • 雜湊警告
  • 使用者定義的事件 82
  • 使用者定義的事件 83
追蹤 (@ 欄),將出現下列的資料行:
  • TextData
  • BinaryData
  • DatabaseID
  • NTUserName
  • ClientProcessID
  • ApplicationName
  • SQLSecurityLoginName
  • SPID
  • 持續時間
  • 開始時間
  • 結束時間
  • 讀取
  • 寫入
  • CPU
  • IntegerData
追蹤會篩選事件,讓它只會擷取事件 ApplicationName 不想在哪裡 'SQL Profiler' (@ filter1),NOT ApplicationName LIKE 'SQLAgent-作業管理員' (@ filter2) NOT ApplicationName LIKE 'SQLAgent-警示引擎' (@ filter3) ApplicationName 不喜歡 'SQLAgent-泛用給重新整理器' (@ filter4) 和 DatabaseID 不等於 4 (@ filter5)。

追蹤將會停止在 2010 Dec.31,(@ stoptime) 下午 12: 00 點。

ActivityTrace 作業指令碼

-- Script generated on 2/9/2001 12:02 PMBEGIN TRANSACTION  DECLARE @JobID BINARY(16)    DECLARE @ReturnCode INT  SELECT @ReturnCode = 0IF (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.'ELSEBEGIN   -- 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 filedeclare @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_outputif (@rc = 0) goto finishset @c = ''echo @blockingcheck     = no > ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @performancecheck  = no >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @tracefile         = C:\ActivityTrace >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @maxfilesize       = 50 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @minMBfree         = 200 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @stoptime          = 2010-12-31 12:00:00.000 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @options           = 2 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @events            = 10,11,12,13,16,17,19,33,42,43,55,82,83 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @columns           = 1,2,3,6,9,10,11,12,13,14,15,16,17,18,25 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @filter1           = 10, 0, 7, N''''SQL Profiler'''' >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @filter2           = 10, 0, 7, N''''SQLAgent - Job Manager'''' >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @filter3           = 10, 0, 7, N''''SQLAgent - Alert Engine'''' >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @filter4           = 10, 0, 7, N''''SQLAgent - Generic Refresher'''' >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @filter5           = 3, 0, 1, 4 >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @job_name          = ActivityTrace >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @schedule00seconds = yes >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @schedule15seconds = no >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @schedule30seconds = no >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputset @c = ''echo @schedule45seconds = no >> ''+@iexec master.dbo.xp_cmdshell @c, no_outputfinish: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 tracedeclare @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 sysnameselect @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 endset @on = 1set @traceid = 0if @stoptime < getdate() goto stoptraceset @cmd1 = ''dir '' + left(@tracefile,2) + '' | find "bytes free"''insert into _t1 exec master.dbo.xp_cmdshell @cmd1select @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) > 0delete _t1 where left([c1],1) != ''@''SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefileif upper(@p) != ''YES'' and upper(@b) != ''YES'' goto stoptraceif @traceid != 0 and @mbfree > @minMBfree goto finishif @mbfree <= @minMBfree goto disableif @traceid != 0 goto finishset @cmd1 = ''if exist '' + @tracefile + ''.trc '' + ''del '' + @tracefile + ''*.trc''exec @rc = master.dbo.xp_cmdshell @cmd1, no_outputif (@rc != 0) goto disableexec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptimeif (@rc != 0) goto disablegoto finishdisable:exec msdb.dbo.sp_update_job @job_name = @job_name, @enabled = 0stoptrace:exec sp_trace_setstatus @traceid, 0exec sp_trace_setstatus @traceid, 2finish:', @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 filtersdeclare @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 intset @on = 1set @traceid = 0select @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 = @tracefileif @traceid = 0 goto finishif (SELECT count(*) FROM ::fn_trace_geteventinfo(@traceid)) > 0 goto finishselect @estart = 1select @enext = charindex('','',@events,@estart)select @cstart = 1select @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 endset @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 = 2while @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 endfinish: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 checkdeclare @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 finishinsert 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''elsebegin 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  endendfinish:', @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 checkdeclare @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 intselect @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 finishinsert 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 > 0while (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 endfinish: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 ENDCOMMIT TRANSACTIONGOTO   EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: 				

警告:本文為自動翻譯

內容

文章識別碼:283696 - 最後檢閱時間:12/05/2015 23:37:04 - 修訂: 3.4

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbmt kbinfo KB283696 KbMtzh
意見反應