如何使用預存程序來監視 SQL Server 2005 中的追蹤

文章翻譯 文章翻譯
文章編號: 912914 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

結論

本文將告訴您,如何使用預存程序來取得下列資訊約一或有關 Microsoft SQL Server 2005 中的所有追蹤:
  • 狀況說明
  • 屬性
  • 追蹤的事件
  • 事件資料行
  • 所套用的篩選器
本文將告訴您,如何建立 追蹤 預存程序,您可以使用來啟動、 停止,及刪除一或所有追蹤。

其他相關資訊

預存程序,如下所示是的追蹤的語法:
追蹤 @ traceid,@ setstatus
備忘稿
  • @ traceid 參數代表追蹤識別數目。資料型別的 @ traceid 參數是整數。預設值為 @ traceid 參數為 0。
  • @ setstatus 參數代表要在追蹤上實作動作。資料型別為整數。預設值為-1。
如果您指定負數的 @ traceid 參數下表顯示。下表列出 追蹤 預存程序會執行的作業。追蹤 預存程序會執行這些作業,根據各種 @ traceid 值的組合 參數和 @ setstatus 值 參數。
摺疊此表格展開此表格
@ traceid@ setstatus作業
>0以外 0,1 或 2 的值列出一個追蹤的資訊。
0以外 0,1 或 2 的值列出所有追蹤資訊。
>00停止一項追蹤。
>01啟動一個追蹤。
>02停止並刪除一個追蹤。
00停止所有追蹤。
01啟動所有追蹤。
02停止並刪除所有追蹤。
比方說,您可以執行下列陳述式,若要停止所有追蹤:
trace 0,0
您可以執行下列陳述式來啟動追蹤其識別碼為 1:
trace 1,1
您可以執行下列陳述式都停止並刪除所有追蹤:
trace 0,2
如果您沒有指定引數,追蹤預存程序傳回所有追蹤下列資訊:
  • 狀況說明
  • 屬性
  • 追蹤的事件
  • 事件資料行
  • 所套用的篩選器
若要建立追蹤預存程序,請依照下列步驟執行:
  1. 開啟 SQL Server 管理 Studio,然後連線至 SQL Server 2005 的執行個體。
  2. Open a new query window, and then run the following statements:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    CREATE proc trace  @traceid int = 0, @setstatus int = -1 as 
    declare @events table (eventid int, [event] varchar(100))
    declare @columns table (columnid int, [column] varchar(100))
    declare @traces table (traceid int, [property] int, [tpdesc] nvarchar(245), check1 bit)
    set nocount on
    
    if @traceid < 0
    begin
    print 'Syntax for trace stored procedure.'
    print 'trace [@traceid], [@setstatus]'
    print 'The default for @traceid is 0.'
    print 'The default for @setstatus is -1.'
    print '@traceid	@setstatus		Operation'
    print '>0		not 0,1, or 2	list information for one trace'
    print '0		not 0,1, or 2	list information all traces'
    print '>0		0		stop one trace'
    print '>0		1		start one trace'
    print '>0		2		stop and delete one trace'
    print '0		0		stop all traces'
    print '0		1		start all traces'
    print '0		2		stop and delete all traces'
    print 'Example 1.  Stop all traces.'
    print 'trace 0,0'
    print 'Example 2.  Start trace number 1.'
    print 'trace 1,1'
    print 'Example 3.  Stop and delete all traces.'
    print 'trace 0,2'
    goto finish
    end
    Insert @events values (10,'RPC:Completed')
    Insert @events values (11,'RPC:Starting')
    Insert @events values (12,'SQL:BatchCompleted')
    Insert @events values (13,'SQL:BatchStarting')
    Insert @events values (14,'Audit Login')
    Insert @events values (15,'Audit Logout')
    Insert @events values (16,'Attention')
    Insert @events values (17,'ExistingConnection')
    Insert @events values (18,'Audit Server Starts and Stops')
    Insert @events values (19,'DTCTransaction')
    Insert @events values (20,'Audit Login Failed')
    Insert @events values (21,'EventLog')
    Insert @events values (22,'ErrorLog')
    Insert @events values (23,'Lock:Released')
    Insert @events values (24,'Lock:Acquired')
    Insert @events values (25,'Lock:Deadlock')
    Insert @events values (26,'Lock:Cancel')
    Insert @events values (27,'Lock:Timeout')
    Insert @events values (28,'Degree of Parallelism Event (7.0 Insert)')
    Insert @events values (33,'Exception')
    Insert @events values (34,'SP:CacheMiss')
    Insert @events values (35,'SP:CacheInsert')
    Insert @events values (36,'SP:CacheRemove')
    Insert @events values (37,'SP:Recompile')
    Insert @events values (38,'SP:CacheHit')
    Insert @events values (39,'Deprecated')
    Insert @events values (40,'SQL:StmtStarting')
    Insert @events values (41,'SQL:StmtCompleted')
    Insert @events values (42,'SP:Starting')
    Insert @events values (43,'SP:Completed')
    Insert @events values (44,'SP:StmtStarting')
    Insert @events values (45,'SP:StmtCompleted')
    Insert @events values (46,'Object:Created')
    Insert @events values (47,'Object:Deleted')
    Insert @events values (50,'SQL Transaction')
    Insert @events values (51,'Scan:Started')
    Insert @events values (52,'Scan:Stopped')
    Insert @events values (53,'CursorOpen')
    Insert @events values (54,'TransactionLog')
    Insert @events values (55,'Hash Warning')
    Insert @events values (58,'Auto Stats')
    Insert @events values (59,'Lock:Deadlock Chain')
    Insert @events values (60,'Lock:Escalation')
    Insert @events values (61,'OLE DB Errors')
    Insert @events values (67,'Execution Warnings')
    Insert @events values (68,'Showplan Text (Unencoded)')
    Insert @events values (69,'Sort Warnings')
    Insert @events values (70,'CursorPrepare')
    Insert @events values (71,'Prepare SQL')
    Insert @events values (72,'Exec Prepared SQL')
    Insert @events values (73,'Unprepare SQL')
    Insert @events values (74,'CursorExecute')
    Insert @events values (75,'CursorRecompile')
    Insert @events values (76,'CursorImplicitConversion')
    Insert @events values (77,'CursorUnprepare')
    Insert @events values (78,'CursorClose')
    Insert @events values (79,'Missing Column Statistics')
    Insert @events values (80,'Missing Join Predicate')
    Insert @events values (81,'Server Memory Change')
    Insert @events values (82,'User Configurable (0-9)')
    Insert @events values (83,'User Configurable (0-9)')
    Insert @events values (84,'User Configurable (0-9)')
    Insert @events values (85,'User Configurable (0-9)')
    Insert @events values (86,'User Configurable (0-9)')
    Insert @events values (87,'User Configurable (0-9)')
    Insert @events values (88,'User Configurable (0-9)')
    Insert @events values (89,'User Configurable (0-9)')
    Insert @events values (90,'User Configurable (0-9)')
    Insert @events values (91,'User Configurable (0-9)')
    Insert @events values (92,'Data File Auto Grow')
    Insert @events values (93,'Log File Auto Grow')
    Insert @events values (94,'Data File Auto Shrink')
    Insert @events values (95,'Log File Auto Shrink')
    Insert @events values (96,'Showplan Text')
    Insert @events values (97,'Showplan All')
    Insert @events values (98,'Showplan Statistics Profile')
    Insert @events values (100,'RPC Output Parameter')
    Insert @events values (102,'Audit Statement GDR Event')
    Insert @events values (103,'Audit Object GDR Event')
    Insert @events values (104,'Audit AddLogin Event')
    Insert @events values (105,'Audit Login GDR Event')
    Insert @events values (106,'Audit Login Change Property Event')
    Insert @events values (107,'Audit Login Change Password Event')
    Insert @events values (108,'Audit Add Login to Server Role Event')
    Insert @events values (109,'Audit Add DB User Event')
    Insert @events values (110,'Audit Add Member to DB Role Event')
    Insert @events values (111,'Audit Add Role Event')
    Insert @events values (112,'Audit App Role Change Password Event')
    Insert @events values (113,'Audit Statement Permission Event')
    Insert @events values (114,'Audit Schema Object Access Event')
    Insert @events values (115,'Audit Backup/Restore Event')
    Insert @events values (116,'Audit DBCC Event')
    Insert @events values (117,'Audit Change Audit Event')
    Insert @events values (118,'Audit Object Derived Permission Event')
    Insert @events values (119,'OLEDB Call Event')
    Insert @events values (120,'OLEDB QueryInterface Event')
    Insert @events values (121,'OLEDB DataRead Event')
    Insert @events values (122,'Showplan XML')
    Insert @events values (123,'SQL:FullTextQuery')
    Insert @events values (124,'Broker:Conversation')
    Insert @events values (125,'Deprecation Announcement')
    Insert @events values (126,'Deprecation Final Support')
    Insert @events values (127,'Exchange Spill Event')
    Insert @events values (128,'Audit Database Management Event')
    Insert @events values (129,'Audit Database Object Management Event')
    Insert @events values (130,'Audit Database Principal Management Event')
    Insert @events values (131,'Audit Schema Object Management Event')
    Insert @events values (132,'Audit Server Principal Impersonation Event')
    Insert @events values (133,'Audit Database Principal Impersonation Event')
    Insert @events values (134,'Audit Server Object Take Ownership Event')
    Insert @events values (135,'Audit Database Object Take Ownership Event')
    Insert @events values (136,'Broker:Conversation Group')
    Insert @events values (137,'Blocked Process Report')
    Insert @events values (138,'Broker:Connection')
    Insert @events values (139,'Broker:Forwarded Message Sent')
    Insert @events values (140,'Broker:Forwarded Message Dropped')
    Insert @events values (141,'Broker:Message Classify')
    Insert @events values (142,'Broker:Transmission')
    Insert @events values (143,'Broker:Queue Disabled')
    Insert @events values (146,'Showplan XML Statistics Profile')
    Insert @events values (148,'Deadlock Graph')
    Insert @events values (149,'Broker:Remote Message Acknowledgement')
    Insert @events values (150,'Trace File Close')
    Insert @events values (152,'Audit Change Database Owner')
    Insert @events values (153,'Audit Schema Object Take Ownership Event')
    Insert @events values (155,'FT:Crawl Started')
    Insert @events values (156,'FT:Crawl Stopped')
    Insert @events values (157,'FT:Crawl Aborted')
    Insert @events values (158,'Audit Broker Conversation')
    Insert @events values (159,'Audit Broker Login')
    Insert @events values (160,'Broker:Message Undeliverable')
    Insert @events values (161,'Broker:Corrupted Message')
    Insert @events values (162,'User Error Message')
    Insert @events values (163,'Broker:Activation')
    Insert @events values (164,'Object:Altered')
    Insert @events values (165,'Performance statistics')
    Insert @events values (166,'SQL:StmtRecompile')
    Insert @events values (167,'Database Mirroring State Change')
    Insert @events values (168,'Showplan XML For Query Compile')
    Insert @events values (169,'Showplan All For Query Compile')
    Insert @events values (170,'Audit Server Scope GDR Event')
    Insert @events values (171,'Audit Server Object GDR Event')
    Insert @events values (172,'Audit Database Object GDR Event')
    Insert @events values (173,'Audit Server Operation Event')
    Insert @events values (175,'Audit Server Alter Trace Event')
    Insert @events values (176,'Audit Server Object Management Event')
    Insert @events values (177,'Audit Server Principal Management Event')
    Insert @events values (178,'Audit Database Operation Event')
    Insert @events values (180,'Audit Database Object Access Event')
    Insert @events values (181,'TM: Begin Tran starting')
    Insert @events values (182,'TM: Begin Tran completed')
    Insert @events values (183,'TM: Promote Tran starting')
    Insert @events values (184,'TM: Promote Tran completed')
    Insert @events values (185,'TM: Commit Tran starting')
    Insert @events values (186,'TM: Commit Tran completed')
    Insert @events values (187,'TM: Rollback Tran starting')
    Insert @events values (188,'TM: Rollback Tran completed')
    Insert @events values (189,'Lock:Timeout (timeout > 0)')
    Insert @events values (190,'Progress Report: Online Index Operation')
    Insert @events values (191,'TM: Save Tran starting')
    Insert @events values (192,'TM: Save Tran completed')
    Insert @events values (193,'Background Job Error')
    Insert @events values (194,'OLEDB Provider Information')
    Insert @events values (195,'Mount Tape')
    Insert @events values (196,'Assembly Load')
    Insert @events values (198,'XQuery Static Type')
    Insert @events values (199,'QN: subscription')
    Insert @events values (200,'QN: parameter table')
    Insert @events values (201,'QN: template')
    Insert @events values (202,'QN: dynamics')
    
    Insert @columns values (1,'TextData')
    Insert @columns values (2,'BinaryData')
    Insert @columns values (3,'DatabaseID')
    Insert @columns values (4,'TransactionID')
    Insert @columns values (5,'LineNumber')
    Insert @columns values (6,'NTUserName')
    Insert @columns values (7,'NTDomainName')
    Insert @columns values (8,'HostName')
    Insert @columns values (9,'ClientProcessID')
    Insert @columns values (10,'ApplicationName')
    Insert @columns values (11,'LoginName')
    Insert @columns values (12,'SPID')
    Insert @columns values (13,'Duration')
    Insert @columns values (14,'StartTime')
    Insert @columns values (15,'EndTime')
    Insert @columns values (16,'Reads')
    Insert @columns values (17,'Writes')
    Insert @columns values (18,'CPU')
    Insert @columns values (19,'Permissions')
    Insert @columns values (20,'Severity')
    Insert @columns values (21,'EventSubClass')
    Insert @columns values (22,'ObjectID')
    Insert @columns values (23,'Success')
    Insert @columns values (24,'IndexID')
    Insert @columns values (25,'IntegerData')
    Insert @columns values (26,'ServerName')
    Insert @columns values (27,'EventClass')
    Insert @columns values (28,'ObjectType')
    Insert @columns values (29,'NestLevel')
    Insert @columns values (30,'State')
    Insert @columns values (31,'Error')
    Insert @columns values (32,'Mode')
    Insert @columns values (33,'Handle')
    Insert @columns values (34,'ObjectName')
    Insert @columns values (35,'DatabaseName')
    Insert @columns values (36,'FileName')
    Insert @columns values (37,'OwnerName')
    Insert @columns values (38,'RoleName')
    Insert @columns values (39,'TargetUserName')
    Insert @columns values (40,'DBUserName')
    Insert @columns values (41,'LoginSid')
    Insert @columns values (42,'TargetLoginName')
    Insert @columns values (43,'TargetLoginSid')
    Insert @columns values (44,'ColumnPermissions')
    Insert @columns values (45,'LinkedServerName')
    Insert @columns values (46,'ProviderName')
    Insert @columns values (47,'MethodName')
    Insert @columns values (48,'RowCounts')
    Insert @columns values (49,'RequestID')
    Insert @columns values (50,'XactSequence')
    Insert @columns values (51,'EventSequence')
    Insert @columns values (52,'BigintData1')
    Insert @columns values (53,'BigintData2')
    Insert @columns values (54,'GUID')
    Insert @columns values (55,'IntegerData2')
    Insert @columns values (56,'ObjectID2')
    Insert @columns values (57,'Type')
    Insert @columns values (58,'OwnerID')
    Insert @columns values (59,'ParentName')
    Insert @columns values (60,'IsSystem')
    Insert @columns values (61,'Offset')
    Insert @columns values (62,'SourceDatabaseID')
    Insert @columns values (63,'SqlHandle')
    Insert @columns values (64,'SessionLoginName')
    
    Insert into @traces 
     select [traceid], [property]
          , [tpdesc]  = 
    	case
    	when [property] = 1 and [value] = 1 then N' produces a rowset.'
    	when [property] = 1 and [value] = 2 then
        N' creates a new file when max file size is reached.'
    	when [property] = 1 and [value] = 3 then
        N' create a new file when max file size is reached and produce a rowset.'
    	when [property] = 1 and [value] = 4 then N' shuts down the trace on an error.'
    	when [property] = 1 and [value] = 5 then N' produces a rowset and shutdown on error.'
    	when [property] = 1 and [value] = 6 then
        N' creates a new file when max file size is reached and shuts down on an error.'
    	when [property] = 1 and [value] = 7 then
        N' creates a new file when max file size is reached, produces a rowset and shuts down on an error.'
    	when [property] = 1 and [value] = 8 then N' is a Blackbox trace.'
    	when [property] = 2 then N' results are in file ' + cast([value] as nvarchar(245)) + N'.'
    	when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(5)) + N' megabytes.'
    	when [property] = 4 and [value] is not null then
        N' automatically stops on ' + cast([value] as nvarchar(25)) + '.'
    	when [property] = 4 and [value] is null then N' does not automatically stop on any date and time.'
    	when [property] = 5 and [value] = 0 then N' is stopped.'
    	when [property] = 5 and [value] = 1 then N' is running.'
    	end
           ,0
    	from :: fn_trace_getinfo(0) order by [property] desc
    
    if (select count(*) from @traces) < 1
    	begin
    		select 'No traces exist.' as [Trace Information]
    		goto finish
    	end
    
    if @traceid > 0
    	begin
    	update @traces set check1 = 1 where [traceid] != @traceid
    	if (select count(*) from @traces where [traceid] = @traceid) < 1
    		begin
    			select 'Trace number ' + cast(@traceid as varchar(3)) + ' does not exist.' as [Trace Information]
    			goto finish
    		end
    	end
    
    if (@setstatus = 0 or @setstatus = 1 or @setstatus = 2) goto changestatus
    
    if @traceid < 1 select 'Trace number '  + cast([traceid] as nvarchar(3)) + [tpdesc]
                        as 'TRACE STATUS' from @traces where [property] = 5
    
    while (select count(*) from @traces where check1 = 0) > 0
    	begin
    		select top 1 @traceid = traceid from @traces where check1 = 0
    		select N'Trace number ' + cast(traceid as nvarchar(3)) + [tpdesc] as 'PROPERTY'
    	    from @traces where traceid = @traceid order by [property] DESC
    		select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' is tracing event '
             + cast(a.eventid as nvarchar(3)) + ', ' + b.[event] + '.' as 'EVENT'
    		  from ::fn_trace_geteventinfo(@traceid) a
    		  left join @events b on a.eventid = b.eventid
    		 group by a.eventid, b.[event]
    		select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' is tracing column '
             + cast(a.columnid as nvarchar(3)) + ', ' + b.[column] + '.' as 'COLUMN'
    		  from ::fn_trace_geteventinfo(@traceid) a
    		  left join @columns b on a.columnid = b.columnid
    		 group by a.columnid, b.[column]
        if (select count(*) from ::fn_trace_getfilterinfo(@traceid)) > 0
    		  select 'Trace number '  + cast(@traceid as nvarchar(3)) + ' filters on column '
      	 	 	   + cast(b.columnid as varchar(3)) +', ' + b.[column] + ', '
      		     + case a.comparison_operator
      					 when 0 then ' Equal '
      					 when 1 then ' Not Equal '
      					 when 2 then ' Greater Than '
      					 when 3 then ' Less Than '
      					 when 4 then ' Greater Than Or Equal '
      					 when 5 then ' Less Than Or Equal '
      					 when 6 then ' LIKE '
      					 when 7 then ' NOT LIKE '
      					 end
      				 + cast(value as varchar(50)) + ' '
    		       + case a.logical_operator
    		         when 0 then 'AND '
      		       when 1 then 'OR '
      		       end
              as 'FILTER'
      		  from ::fn_trace_getfilterinfo(@traceid) a
      		  left join @columns b on a.columnid = b.columnid
        else
          begin
            print 'Trace number '  + cast(@traceid as nvarchar(3)) + ' has no filters.'
            print ''
          end
    		update @traces set check1 = 1 where traceid = @traceid
    	end
    goto finish
    changestatus:
    while (select count(*) from @traces where check1 = 0) > 0
      begin
    		select top 1 @traceid = traceid from @traces where check1 = 0
    		update @traces set check1 = 1 where [traceid] = @traceid
        if @setstatus = 0 and @traceid <> 1
         	begin
    	     	exec sp_trace_setstatus @traceid, 0
           	select 'Trace number ' + cast(@traceid as varchar(3)) + ' is stopped.' as [Trace Information]
         	end
        if @setstatus = 1 and @traceid <> 1
         	begin
           	exec sp_trace_setstatus @traceid, 1
           	select 'Trace number ' + cast(@traceid as varchar(3)) + ' is running.' as [Trace Information]
         	end
    		if @setstatus = 2 and @traceid <> 1
    			begin
           	exec sp_trace_setstatus @traceid, 0
           	exec sp_trace_setstatus @traceid, 2
           	select 'Trace number ' + cast(@traceid as varchar(3)) + ' was stopped and deleted.' as [Trace Information]
    			end
    	end
    finish:
    set nocount off
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO

?考

如需有關如何使用預存程序來監視 SQL Server 2000 中的追蹤的詳細資訊,按一下 [下列面的文件編號,檢視 「 Microsoft 知識庫 」 中的發行項]:
283786INF: 如何監視 SQL Server 2000 的追蹤
如需有關 fn_trace_getinfo 函式的詳細資訊,請造訪下列 Microsoft 開發 o 人 h 員 ? 工 u 具 ? 網路 (MSDN) 網站]:
http://msdn2.microsoft.com/en-us/library/ms173875.aspx
如需有關 sp_trace_setevent 預存程序的詳細資訊,請造訪下列 MSDN 網站:
http://msdn2.microsoft.com/en-us/library/ms186265.aspx
如需有關 sp_trace_setfilter 預存程序的詳細資訊,請造訪下列 MSDN 網站:
http://msdn2.microsoft.com/en-us/library/aa260322(SQL.80).aspx

屬性

文章編號: 912914 - 上次校閱: 2007年9月4日 - 版次: 1.1
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
關鍵字:?
kbmt kbexpertiseadvanced kbhowto kbinfo KB912914 KbMtzh
機器翻譯
重要:本文是以 Microsoft 機器翻譯軟體翻譯而成,而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章,讓使用者可以依其使用語言使用知識庫中的所有文章。但是,機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤,就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
按一下這裡查看此文章的英文版本:912914
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