Re: SP_Trace_create

From: Gert E.R. Drapers (GertD_at_SQLDev.Net)
Date: 09/27/04

• Next message: vishal: "SQL 2000 Recovery Model"
• Previous message: vishal: "SP_Trace_create"
• In reply to: vishal: "SP_Trace_create"
• Messages sorted by: [ date ] [ thread ]

Date: Mon, 27 Sep 2004 12:37:02 +0200

See the attached SP.

--
***************************************************************************
-- Copyright (C) 1991-2004 SQLDEV.NET

-- 
-- file: sp_trace_blackbox.sql
-- descr.: SQL Server 2000 Black Box trace
-- author: Gert E.R. Drapers (GertD@SQLDev.Net)
--
-- @@bof_revsion_marker
-- revision history
-- yyyy/mm/dd  by       description
-- ==========  ======= 
==========================================================
-- 2002/04/30  gertd    v1.0.0.0 created
-- @@eof_revsion_marker
--  
***************************************************************************
use master
go
if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_trace_blackbox]') and objectproperty(id, 
N'IsProcedure') = 1)
 drop procedure [dbo].[sp_trace_blackbox]
go
-- If no argument is passed to the @on parameter then get the current 
blackbox trace status.
-- If @on is zero then stop and delete the blackbox trace.
-- If @on is one then create and start the blackbox trace.
create procedure [dbo].[sp_trace_blackbox] @on int = 2
as
set nocount on
declare @traceid int,
  @blackboxstatus int,
  @dir nvarchar(80)
select  @traceid = 0,
  @blackboxstatus = 0
select  @traceid = traceid
from  ::fn_trace_getinfo(0)
where  property = 1
and  value = 8
if ((@on = 0) and (@traceid > 0))
begin
 select @blackboxstatus = cast(value as int)
 from  ::fn_trace_getinfo(0)
    where  traceid = @traceid
 and  property = 5
   if (@blackboxstatus > 0)
  exec sp_trace_setstatus @traceid, 0 -- stop blackbox trace
 exec sp_trace_setstatus @traceid, 2 -- delete blackbox trace definition
end
if (@on = 1)
begin
 if (@traceid < 1)
  exec sp_trace_create @traceid output, 8 -- create blackbox trace
 exec sp_trace_setstatus @traceid, 1 -- start blackbox trace
end
select @traceid = 0,
  @blackboxstatus = 0
select  @traceid = traceid
from  ::fn_trace_getinfo(0)
where  property = 1
and  value = 8
select  @blackboxstatus = cast(value as int)
from  ::fn_trace_getinfo(0)
where  traceid = @traceid
and  property = 5
if ((@traceid > 0) and (@blackboxstatus > 0))
begin
 select  @dir = cast(value as nvarchar(80))
 from  ::fn_trace_getinfo(0)
 where  traceid = @traceid
 and  property = 2
    print  N'The blackbox trace is running, the trace file is location is ' 
+ @dir + '.trc'
end
else
begin
 print  N'The blackbox trace is not running.'
end
go
exec sp_trace_blackbox
exec sp_trace_blackbox @on = 1
exec sp_trace_blackbox
exec sp_trace_blackbox @on = 0
exec sp_trace_blackbox
-- trace ID running the Black Box trace
select  *
from  ::fn_trace_getinfo(default)
where  property = 1
and  value = 8
GertD@SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"vishal" <compaq@smil.co.in> wrote in message 
news:Od7WNYHpEHA.800@TK2MSFTNGP14.phx.gbl...
> Hello everybody
>
> how do i write the command sp_trace_create in qa to start trace with
> trace_produce_blackbox.
> How do i disable this command.
>
> 
begin 666 sp_trace_blackbox.sql
M+2T@*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ
M*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ#0HM+2!#;W!Y<FEG
M:'0@*$,I(#$Y.3$M,C P-"!344Q$158N3D54#0HM+2 -"BTM(&9I;&4Z"7-P
M7W1R86-E7V)L86-K8F]X+G-Q; T*+2T@9&5S8W(N.@E344P@4V5R=F5R(#(P
M,# @0FQA8VL@0F]X('1R86-E#0HM+2!A=71H;W(Z"4=E<G0@12Y2+B!$<F%P
M97)S("A'97)T1$!344Q$978N3F5T*0T*+2T-"BTM($! 8F]F7W)E=G-I;VY?
M;6%R:V5R#0HM+2!R979I<VEO;B!H:7-T;W)Y#0HM+2!Y>7EY+VUM+V1D("!B
M>2 @(" @("!D97-C<FEP=&EO;@T*+2T@/3T]/3T]/3T]/2 @/3T]/3T]/2 @
M/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]/3T]
M/3T]/3T]/3T]/3T]/0T*+2T@,C P,B\P-"\S," @9V5R=&0@(" @=C$N,"XP
M+C @8W)E871E9 T*+2T@0$!E;V9?<F5V<VEO;E]M87)K97(-"BTM("HJ*BHJ
M*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ
M*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*@T*=7-E(&UA<W1E<@T*9V\-"@T*
M:68@97AI<W1S("AS96QE8W0@*B!F<F]M(&1B;RYS>7-O8FIE8W1S('=H97)E
M(&ED(#T@;V)J96-T7VED*$XG6V1B;UTN6W-P7W1R86-E7V)L86-K8F]X72<I
M(&%N9"!O8FIE8W1P<F]P97)T>2AI9"P@3B=)<U!R;V-E9'5R92<I(#T@,2D-
M"@ED<F]P('!R;V-E9'5R92!;9&)O72Y;<W!?=')A8V5?8FQA8VMB;WA=#0IG
M;PT*#0HM+2!)9B!N;R!A<F=U;65N="!I<R!P87-S960@=&\@=&AE($!O;B!P
M87)A;65T97(@=&AE;B!G970@=&AE(&-U<G)E;G0@8FQA8VMB;W@@=')A8V4@
M<W1A='5S+@T*+2T@268@0&]N(&ES('IE<F\@=&AE;B!S=&]P(&%N9"!D96QE
M=&4@=&AE(&)L86-K8F]X('1R86-E+@T*+2T@268@0&]N(&ES(&]N92!T:&5N
M(&-R96%T92!A;F0@<W1A<G0@=&AE(&)L86-K8F]X('1R86-E+@T*#0IC<F5A
M=&4@<')O8V5D=7)E(%MD8F]=+EMS<%]T<F%C95]B;&%C:V)O>%T@0&]N(&EN
M=" ](#(@#0IA<PT*#0IS970@;F]C;W5N="!O;@T*#0ID96-L87)E($!T<F%C
M96ED(&EN="P@#0H)"4!B;&%C:V)O>'-T871U<R!I;G0L( T*"0E 9&ER(&YV
M87)C:&%R*#@P*0T*#0IS96QE8W0@"4!T<F%C96ED(#T@,"P-"@D)0&)L86-K
M8F]X<W1A='5S(#T@, T*#0IS96QE8W0@"4!T<F%C96ED(#T@=')A8V5I9" -
M"F9R;VT@"3HZ9FY?=')A8V5?9V5T:6YF;R@P*0T*=VAE<F4@"7!R;W!E<G1Y
M(#T@,2 -"F%N9" )=F%L=64@/2 X#0H-"FEF("@H0&]N(#T@,"D@86YD("A 
M=')A8V5I9" ^(# I*0T*8F5G:6X-"@ES96QE8W0)0&)L86-K8F]X<W1A='5S
M(#T@8V%S="AV86QU92!A<R!I;G0I( T*"69R;VT@"3HZ9FY?=')A8V5?9V5T
M:6YF;R@P*0T*(" @"7=H97)E( ET<F%C96ED(#T@0'1R86-E:60@#0H)86YD
M( EP<F]P97)T>2 ](#4-"@T*(" ):68@*$!B;&%C:V)O>'-T871U<R ^(# I
M#0H)"65X96,@<W!?=')A8V5?<V5T<W1A='5S($!T<F%C96ED+" P("TM('-T
M;W @8FQA8VMB;W@@=')A8V4-"@D-"@EE>&5C('-P7W1R86-E7W-E='-T871U
M<R! =')A8V5I9"P@,B M+2!D96QE=&4@8FQA8VMB;W@@=')A8V4@9&5F:6YI
M=&EO;@T*96YD#0H-"FEF("A ;VX@/2 Q*0T*8F5G:6X-"@EI9B H0'1R86-E
M:60@/" Q*0T*"0EE>&5C('-P7W1R86-E7V-R96%T92! =')A8V5I9"!O=71P
M=70L(#@@+2T@8W)E871E(&)L86-K8F]X('1R86-E#0H)#0H)97AE8R!S<%]T
M<F%C95]S971S=&%T=7,@0'1R86-E:60L(#$@+2T@<W1A<G0@8FQA8VMB;W@@
M=')A8V4-"F5N9 T*#0IS96QE8W0)0'1R86-E:60@/2 P+ T*"0E 8FQA8VMB
M;WAS=&%T=7,@/2 P#0H-"G-E;&5C=" )0'1R86-E:60@/2!T<F%C96ED( T*
M9G)O;2 ).CIF;E]T<F%C95]G971I;F9O*# I#0IW:&5R92 )<')O<&5R='D@
M/2 Q( T*86YD( EV86QU92 ](#@-"@T*<V5L96-T( E 8FQA8VMB;WAS=&%T
M=7,@/2!C87-T*'9A;'5E(&%S(&EN="D@#0IF<F]M( DZ.F9N7W1R86-E7V=E
M=&EN9F\H,"D-"G=H97)E( ET<F%C96ED(#T@0'1R86-E:60@#0IA;F0@"7!R
M;W!E<G1Y(#T@-0T*#0II9B H*$!T<F%C96ED(#X@,"D@86YD("A 8FQA8VMB
M;WAS=&%T=7,@/B P*2D-"F)E9VEN#0H)<V5L96-T( E 9&ER(#T@8V%S="AV
M86QU92!A<R!N=F%R8VAA<B@X,"DI( T*"69R;VT@"3HZ9FY?=')A8V5?9V5T
M:6YF;R@P*0T*"7=H97)E( ET<F%C96ED(#T@0'1R86-E:60@#0H)86YD( EP
M<F]P97)T>2 ](#(-"@T*(" @('!R:6YT( E.)U1H92!B;&%C:V)O>"!T<F%C
M92!I<R!R=6YN:6YG+"!T:&4@=')A8V4@9FEL92!I<R!L;V-A=&EO;B!I<R G
M("L@0&1I<B K("<N=')C)PT*96YD#0IE;'-E#0IB96=I;B -"@EP<FEN=" )
M3B=4:&4@8FQA8VMB;W@@=')A8V4@:7,@;F]T(')U;FYI;F<N)PT*96YD#0IG
M;PT*#0H-"F5X96,@<W!?=')A8V5?8FQA8VMB;W@-"F5X96,@<W!?=')A8V5?
M8FQA8VMB;W@@0&]N(#T@,0T*97AE8R!S<%]T<F%C95]B;&%C:V)O> T*97AE
M8R!S<%]T<F%C95]B;&%C:V)O>"! ;VX@/2 P#0IE>&5C('-P7W1R86-E7V)L
M86-K8F]X#0H-"BTM('1R86-E($E$(')U;FYI;F<@=&AE($)L86-K($)O>"!T
M<F%C90T*<V5L96-T( DJ( T*9G)O;2 ).CIF;E]T<F%C95]G971I;F9O*&1E
M9F%U;'0I#0IW:&5R92 )<')O<&5R='D@/2 Q( T*86YD( EV86QU92 ](#@-
!"@``
`
end

• Next message: vishal: "SQL 2000 Recovery Model"
• Previous message: vishal: "SP_Trace_create"
• In reply to: vishal: "SP_Trace_create"
• Messages sorted by: [ date ] [ thread ]

• Windows
• Science
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint