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

중요: 본 문서는 전문 번역가가 번역한 것이 아니라 Microsoft 기계 번역 소프트웨어로 번역한 것입니다. Microsoft는 번역가가 번역한 문서 및 기계 번역된 문서를 모두 제공하므로 Microsoft 기술 자료에 있는 모든 문서를 한글로 접할 수 있습니다. 그러나 기계 번역 문서가 항상 완벽한 것은 아닙니다. 따라서 기계 번역 문서에는 마치 외국인이 한국어로 말할 때 실수를 하는 것처럼 어휘, 구문 또는 문법에 오류가 있을 수 있습니다. Microsoft는 내용상의 오역 또는 Microsoft 고객이 이러한 오역을 사용함으로써 발생하는 부 정확성, 오류 또는 손해에 대해 책임을 지지 않습니다. Microsoft는 이러한 문제를 해결하기 위해 기계 번역 소프트웨어를 자주 업데이트하고 있습니다.

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 GOSET ANSI_NULLS ON GOCREATE 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 onif @traceid < 0beginprint '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 finishendInsert @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] descif (select count(*) from @traces) < 1	begin		select 'No traces exist.' as [Trace Information]		goto finish	endif @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	endif (@setstatus = 0 or @setstatus = 1 or @setstatus = 2) goto changestatusif @traceid < 1 select 'Trace number '  + cast([traceid] as nvarchar(3)) + [tpdesc]                    as 'TRACE STATUS' from @traces where [property] = 5while (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	endgoto finishchangestatus: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	endfinish:set nocount offGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
참조
저장된 프로시저를 사용하여 SQL Server 2000 추적에서 모니터링하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
283786INF: SQL Server 2000 추적을 모니터링하는 방법
fn_trace_getinfo 함수에 대한 자세한 내용은 다음 MSDN) Microsoft 개발자 네트워크 (웹 사이트를 방문하십시오. 저장된 sp_trace_setevent 절차에 대한 자세한 내용은 다음 MSDN 웹 사이트를 방문하십시오. 저장된 sp_trace_setfilter 절차에 대한 자세한 내용은 다음 MSDN 웹 사이트를 방문하십시오.

경고: 이 문서는 자동으로 번역되었습니다.

속성

문서 ID: 912914 - 마지막 검토: 12/09/2015 04:18:22 - 수정: 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

  • kbnosurvey kbarchive kbmt kbexpertiseadvanced kbhowto kbinfo KB912914 KbMtko
피드백