저장된 프로시저를 사용하여 SQL Server 2005 추적에서 모니터링하는 방법을

기술 자료 번역 기술 자료 번역
기술 자료: 912914 - 이 문서가 적용되는 제품 보기.
모두 확대 | 모두 축소

요약

다음 문서에서는 저장된 프로시저를 한 또는 Microsoft SQL Server 2005의 모든 추적에 대한 다음 정보를 얻을 수 있습니다.
  • 상태
  • 속성
  • 추적할지 이벤트
  • 이벤트 열
  • 적용된 필터
이 문서에서는 시작, 중지 및 하나 또는 모든 추적을 삭제하는 데 사용할 수 추적 저장 프로시저를 만드는 방법을 설명합니다.

추가 정보

추적 저장 프로시저에 대한 구문은 다음과 같습니다.
추적 @ traceid, @ setstatus
슬라이드 노트
  • @ traceid 추적의 ID 매개 변수를 나타냅니다. @ traceid 데이터 형식을 정수 매개 변수입니다. @ traceid의 기본값은 매개 변수가 0입니다.
  • @ setstatus 매개 변수에 대한 추적 구현하려면 동작을 나타냅니다. 정수 데이터 형식입니다. 기본 값은-1입니다.
@ traceid 음수를 지정하면 매개 변수, 다음 표에 표시됩니다. 테이블에 저장된 추적 절차를 수행하는 작업을 나열합니다. 추적 저장 프로시저를 @ traceid 값 다양한 조합을 기반으로 이러한 작업을 수행하는 매개 변수 및 @ setstatus의 값은 매개 변수.
표 축소표 확대
@ traceid@ setstatus작업
>00,1, 또는 2 이외의 값한 추적 정보를 나열하십시오.
00,1, 또는 2 이외의 값모든 추적 정보를 나열하십시오.
>00한 추적을 중지하십시오.
>01한 추적을 시작하십시오.
>02중지 및 한 추적 삭제하십시오.
00모든 추적을 중지하십시오.
01모든 추적을 시작하십시오.
02중지하고 모든 추적을 삭제하십시오.
예를 들어, 모든 추적을 중지한 다음 문을 실행할 수 있습니다: 1
trace 0,0
해당 ID 번호가 추적을 시작하려면 다음 문을 실행할 수 있습니다:
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 함수에 대한 자세한 내용은 다음 MSDN) Microsoft 개발자 네트워크 (웹 사이트를 방문하십시오.
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 KbMtko
기계 번역된 문서
중요: 본 문서는 전문 번역가가 번역한 것이 아니라 Microsoft 기계 번역 소프트웨어로 번역한 것입니다. Microsoft는 번역가가 번역한 문서 및 기계 번역된 문서를 모두 제공하므로 Microsoft 기술 자료에 있는 모든 문서를 한글로 접할 수 있습니다. 그러나 기계 번역 문서가 항상 완벽한 것은 아닙니다. 따라서 기계 번역 문서에는 마치 외국인이 한국어로 말할 때 실수를 하는 것처럼 어휘, 구문 또는 문법에 오류가 있을 수 있습니다. 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