SQL Server 黑盒跟踪 -- 深入进阶

renran421

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trace_blackbox]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)　　
drop procedure [dbo].[trace_blackbox]
　　
GO
　　
SET QUOTED_IDENTIFIER ON
　　
GO
　　
SET ANSI_NULLS ON
　　
GO
　　
CREATE PROCEDURE trace_blackbox @on int = 2 AS
　　
/* 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.
　　
*/
　　
declare @traceid int, @blackboxstatus int, @dir nvarchar(80)
　　
set @traceid = 0
　　
set @blackboxstatus = 0
　　
set nocount on
　　
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
　　
set @traceid = 0
　　
set @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
　　
select 'The blackbox trace is running and the trace file is in the following directory.'
　　
select @dir + '.trc'
　　
end
　　
ELSE select 'The blackbox trace is not running.'
　　
set nocount off
　　
GO
　　
SET QUOTED_IDENTIFIER OFF
　　
GO
　　
SET ANSI_NULLS ON
　　
GO