لطفا منتظر باشید ...
Sp_start_trace
SQL Server's Profiler utility is a powerful tool and a vast improvement over the SQL Trace facility that came with releases prior to 7.0. However, it's also a resource-intensive and sometimes painfully slow application. Because I often need to trace the activity on SQL Server in situations where I don't wish to incur the overhead of the Profiler GUI, I wrote my own set of stored procedures to manage Profiler traces. They allow you to start, stop, and list Profiler traces without actually using Profiler itself. How do they work? By calling SQL Server's sp_trace_% extended procedures, the same way that Profiler itself does. These procedures are documented in the Books Online and are considerably easier to use than previous versions.
My trace management code consists of three procedures: sp_start_trace, sp_stop_trace, and sp_list_trace. They each do what their names imply. Here's the code for sp_start_trace (Listing 21-4):
Listing 21-4 sp_start_trace.
USE master
GO
IF OBJECT_ID('sp_start_trace') IS NOT NULL
DROP PROC sp_start_trace
GO
CREATE PROC sp_start_trace @FileName sysname=NULL,
@TraceName sysname='tsqltrace',
@Options int=2,
@MaxFileSize bigint=5,
@StopTime datetime=NULL,
@Events varchar(300)=
-- 11 - RPC:Starting
-- 13 - SQL:BatchStarting
-- 14 - Connect
-- 15 - Disconnect
-- 16 - Attention
-- 17 - Existing Connection
-- 33 - Exception
-- 42 - SP:Starting
-- 43 - SP:Completed
-- 45 - SP:StmtCompleted
-- 55 - Hash Warning
-- 67 - Execution Warnings
-- 69 - Sort Warnings
-- 79 - Missing Column Statistics
-- 80 - Missing Join Predicate
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,
12,13,14,15,16,17,18,19,20,21
,22,23,24,25,26,27,28,29,30,31
,32,33,34,35,36,37,38,39,40,41,42,43,44,',
@IncludeTextFilter sysname=NULL,
@ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL,
@ExcludeObjIdFilter int=NULL,
@TraceId int = NULL
AS
SET NOCOUNT ON
IF @FileName='/?' GOTO Help
-- Declare variables
DECLARE @OldQueueHandle int --
Queue handle of currently running trace queue
DECLARE @QueueHandle int --
Queue handle for new running trace queue
DECLARE @On bit -
- Necessary because of a bug in some of the sp_trace_xx procs
DECLARE @OurObjId int --
Used to keep us out of the trace log
DECLARE @OldTraceFile sysname
-- File name of running trace
DECLARE @res int --
Result var for sp calls
SET @On=1
-- Do some basic param validation
IF (@Cols IS NULL) BEGIN
RAISERROR('You must specify the columns to trace.',16,10)
RETURN -1
END
IF (@Events IS NULL) BEGIN
RAISERROR('You must specify a list of
trace events in @Events.',16,10)
RETURN -1
END
-- Append the datetime to the file name to
create a new, unique file name.
IF @FileName IS NULL
SELECT @FileName = 'c:\TEMP\tsqltrace_'
+ CONVERT(CHAR(8),getdate(),112) +
REPLACE(CONVERT(varchar(15),getdate(),114),':','')
-- Create the trace queue
EXEC @res=sp_trace_create @traceid=
@QueueHandle OUT, @options=@Options,
@tracefile=@FileName, @maxfilesize=
@MaxFileSize, @stoptime=@StopTime
IF @res<>0 BEGIN
IF @res=1 PRINT 'Trace not
started. Reason: Unknown error.'
ELSE IF @res=10 PRINT 'Trace not
started. Reason: Invalid options.Returned
when options specified are incompatible.'
ELSE IF @res=12 PRINT 'Trace
not started. Reason: Error creating file.
Returned if the file already exists,
drive is out of space, or path does not exist.'
ELSE IF @res=13 PRINT 'Trace
not started. Reason: Out of memory. Returned
when there is not enough memory
to perform the specified action.'
ELSE IF @res=14 PRINT 'Trace
not started. Reason: Invalid stop time.
Returned when the stop time
specified has already happened.'
ELSE IF @res=15 PRINT 'Trace
not started. Reason: Invalid parameters.
Returned when the user
supplied incompatible parameters.'
RETURN @res
END
PRINT 'Trace started.'
PRINT 'The trace file name is : '+@FileName+'.'
-- Specify the event classes and columns to trace
IF @Events IS NOT NULL BEGIN
-- Loop through the @Events and
@Cols strings, parsing out each event & column
number and adding them to the trace definition
IF RIGHT(@Events,1)<>',' SET
@Events=@Events+',' -- Append comma for the loop
IF RIGHT(@Cols,1)<>',' SET
@Cols=@Cols+',' -- Append comma for the loop
DECLARE @i int, @j int,
@Event int, @Col int, @ColStr varchar(300)
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @ColStr=@Cols
SET @j=CHARINDEX(',',@ColStr)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)
EXEC sp_trace_setevent
@traceid=@QueueHandle, @eventid=@Event,
@columnid=@Col, @on=@On
SET @ColStr=SUBSTRING(@ColStr,@j+1,300)
SET @j=CHARINDEX(',',@ColStr)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
END
-- Set filters (default values
avoid tracing the trace activity itself)
-- You can specify other filters like
application name, etc., by supplying
strings to the @IncludeTextFilter/
@ExcludeTextFilter parameters, separated by semicolons
SET @ExcludeTextFilter='sp_
%trace%'+ISNULL(';'+@ExcludeTextFilter,'')
-- By default, keep our own activity from showing up
SET @OurObjId=OBJECT_
ID('master..sp_start_trace')
EXEC sp_trace_setfilter
@traceid=@QueueHandle, @columnid=1,
@logical_operator=0,
@comparison_operator=7,
@value=@ExcludeTextFilter
EXEC sp_trace_setfilter
@traceid=@QueueHandle, @columnid=1,
@logical_operator=0,
@comparison_operator=7,
@value=N'EXEC% sp_%trace%'
IF @IncludeTextFilter IS NOT NULL
EXEC sp_trace_setfilter
@traceid=@QueueHandle, @columnid=1,
@logical_operator=0,
@comparison_operator=6,
@value=@IncludeTextFilter
IF @IncludeObjIdFilter IS NOT NULL
EXEC sp_trace_setfilter
@traceid=@QueueHandle, @columnid=22,
@logical_operator=0,
@comparison_operator=0, @value=@IncludeObjIdFilter
EXEC sp_trace_setfilter
@traceid=@QueueHandle, @columnid=22,
@logical_operator=0,
@comparison_operator=1, @value=@OurObjId
IF @ExcludeObjIdFilter IS NOT NULL
EXEC sp_trace_setfilter
@traceid=@QueueHandle, @columnid=22,
@logical_operator=0,
@comparison_operator=1,
@value=@ExcludeObjIdFilter
-- Turn on the trace
EXEC sp_trace_setstatus
@traceid=@QueueHandle, @status=1
-- Record the trace queue
handle for subsequent jobs. (This allows us to know
how to stop the trace.)
IF OBJECT_ID('tempdb..TraceQueue') IS NULL BEGIN
CREATE TABLE tempdb..TraceQueue
(TraceID int, TraceName varchar(20), TraceFile sysname)
INSERT tempdb..TraceQueue
VALUES(@QueueHandle, @TraceName, @FileName)
END ELSE BEGIN
IF EXISTS(SELECT * FROM
tempdb..TraceQueue WHERE TraceName = @TraceName)
BEGIN
UPDATE tempdb..TraceQueue
SET TraceID = @QueueHandle, TraceFile=@FileName
WHERE TraceName = @TraceName
END ELSE BEGIN
INSERT tempdb..TraceQueue
VALUES(@QueueHandle, @TraceName, @FileName)
END
END
RETURN 0
Help:
/*
Code abridged
*/
RETURN -1
GO
exec sp_start_trace
(Results)
Trace started.
The trace file name is : c:\TEMP\tsqltrace_20001204011454350.
By default, sp_start_trace initiates a Profiler trace that includes all trace columns and a stock set of trace events, but you can change this by specifying the @Events and/or @Cols parameters. Both are comma-delimited lists of event/column numbers. You can look up these numbers in the Books Online.
If you don't specify your own trace output file name, sp_start_trace attempts to create one for you in C:\TEMP using the current date and time to label the file. Trace files can grow to be quite large, so you should be careful to direct the file to a drive where you have plenty of space. Understand that the path that sp_start_trace uses is relative to the SQL Server on which the trace will run, not your local machine. Unlike Profiler, sp_start_trace always runs traces exclusively on the server.
