|
|
Trace Messages Part V: Purging Trace Tables
Volume 3 #7
February 15,
2005 Full TOC
by Andrew Novick
Sign up for this newsletter at:
http://www.novicksoftware.com/coding-in-sql/coding-in-sql-signup.htm
The first four
articles in this series have been about writing trace information to
the AppSession, AppSessionTrace and AppSession. I've found this a
useful technique for developing and using non-trivial stored
procedures. If I keep the traces turned on in production, all those
rows tend to add up after a while. Usp_AppSession_Purge has the
task of cleaning up the AppSession* tables. Here's the CREATE PROC
script:
CREATE PROCEDURE
dbo.usp_AppSession_Purge
@myTLevel int = 3
-- Trace Level for this sp
, @PurgSes BIT = 0
-- Purge on sessions
, @PurgTrc BIT = 0
-- Purge on Traces
, @PurgMsr BIT = 0
-- Purge on Measurements
, @DelTrcLev int = -1
-- Delete Msgs >= this level
, @DaysAgo int = 31
-- How many days to go back
, @TraceSesID int OUTPUT
-- ID for this job.
/*
* Purges the AppSession, AppSessionTrace, and
* AppSesssionMeasurement tables back to @DaysAgo.
*
* Example:
DECLARE @TID int, @RC int
SET NOCOUNT ON
exec @RC = dbo.usp_AppSession_Purge -3, 1, 1, 1, 3
, 31, @TID OUTPUT
exec usp_AppSession_RPt @TID
*
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
* Published as Coding-in-SQL Newsletter Vol 3 #7 2/15/05
http://www.NovickSoftware.com/coding-in-sql.htm
****************************************************************/
AS
DECLARE @myError int
, @myRowCount int
, @Msg nvarchar(3000)
, @WorstError int
, @RC int
, @TotalRows int
, @TraceSource varchar(24)
, @TraceCategory varchar(16)
, @TraceMsgID int
-- ID of each message
, @JobStartTime datetime
, @NumDelSes int
-- # AppSession rows deleted.
, @NumDelTrc int
-- # AppSessionTrace rows deleted.
, @NumDelMsr int
-- # AppSessionMeasurement rows deleted.
, @FromDT datetime
-- Datetime to delete before.
SET NOCOUNT ON
SELECT @TotalRows = 0
, @JobStartTime = getdate()
, @TraceSource = 'usp_AppSession_Purge'
, @TraceCategory = 'SP'
, @NumDelSes = 0
, @NumDelTrc = 0
, @NumDelMsr = 0
, @WorstError = 0
-- @DaysAgo from today and
moved to start of day
, @FromDT = CONVERT(datetime
, left(convert(char(10)
, DATEADD(day,
-1 * @DaysAgo, getdate())
, 120
)
, 10
)
, 120
)
-- Create the session
EXEC usp_AppSession_SQLJOB 0,
@TraceSource
, USER_ID,
@TraceSesID OUTPUT
-- Write the startup message
SET @Msg =
+'PurgeSes=' + coalesce(convert(varchar, @PurgSes),'<null>')
+' PurgeTrc=' + coalesce(convert(varchar,
@PurgSes),'<null>')
+' PurgeMsr=' + coalesce(convert(varchar,
@PurgSes),'<null>')
+' DelTrcLev='+coalesce(convert(varchar(20),@DelTrcLev),'<null>')
+' DaysAgo='+coalesce(convert(varchar(20),@DaysAgo),'<null>')
+' FromDT='+coalesce(convert(varchar(20),@FromDT,121),'<null>')
+' SesID='+coalesce(convert(varchar(10),
@TraceSesID),'<null>')
EXEC usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel, 3, @Msg, @TraceSource, @TraceCategory
-----------------------------------------------------------------
-----------------------------------------------------------------
-- Remove Trace Messages
IF @PurgTrc=1
BEGIN
-- Delete messages created
before start of day @DaysAgo.
DELETE FROM
AppSessionTrace
WHERE MsgDT <= @FromDT
AND MessageTraceLevel >= @DelTrcLev
SELECT @myError = @@Error, @myRowCount
= @@Rowcount
IF @myError = 0
BEGIN
SET @NumDelMsr = @myRowCount
SET @Msg = 'Deleted '
+ CONVERT(VARCHAR,
@NumDelTrc)
+ ' rows from AppSessionTrace.'
exec usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel , 2,@Msg,@TraceSource,@TraceCategory
END
ELSE BEGIN
SET @Msg = 'Error
deleting from AppSessionTrace '
+ convert(varchar(10),
@myError)
exec usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel, 1,@Msg,@TraceSource,@TraceCategory
RAISERROR (@Msg, 16, 1)
SET @WorstError =
CASE WHEN @WorstError < @myError
THEN
@MyError ELSE @WorstError
END
GOTO GETOUT
END
END
-----------------------------------------------------------------
-----------------------------------------------------------------
-- Remove the AppSessionMeasurement rows
IF @PurgSes=1
BEGIN
-- Delete Measurements for
sessions before start of day
-- @DaysAgo. Also any orphan
(no Appsession row) rows.
DELETE M
FROM AppSessionMeasurement M
LEFT OUTER JOIN AppSession
S
ON M.SessionID =
S.SessionID
WHERE S.SessionID
IS NULL
OR S.CreatedDT
<= @FromDT
SELECT @myError = @@Error, @myRowCount
= @@Rowcount
IF @myError = 0
BEGIN
SET @NumDelMsr = @myRowCount
SET @Msg = 'Deleted '
+ CONVERT(VARCHAR,
@myRowCount)
+ ' rows from AppSessionMeasurement'
EXEC usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel , 2,@Msg,@TraceSource,@TraceCategory
END
ELSE BEGIN
SET @Msg = 'Error
deleting from AppSessionMeasurement '
+ convert(varchar(10),
@myError)
EXEC usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel, 1,@Msg,@TraceSource,@TraceCategory
RAISERROR (@Msg, 16, 1)
SET @WorstError =
CASE WHEN @WorstError < @myError
THEN
@MyError ELSE @WorstError
END
GOTO GETOUT
END
END
-----------------------------------------------------------------
-----------------------------------------------------------------
-- Remove the AppSession rows
IF @PurgSes=1
BEGIN
-- Delete messages created
before start of day @DaysAgo.
DELETE FROM AppSession
WHERE CreatedDT <= @FromDT
SELECT @myError = @@Error, @myRowCount
= @@Rowcount
IF @myError = 0
BEGIN
SET @NumDelSes = @myRowCount
SET @Msg = 'Deleted '
+ CONVERT(VARCHAR,
@myRowCount)
+ ' rows from AppSession.'
EXEC usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel , 2,@Msg,@TraceSource,@TraceCategory
END
ELSE BEGIN
SET @Msg = 'Error
deleting from AppSession '
+ convert(varchar(10),
@myError)
EXEC usp_AppSession_Trace @TraceMsgID
output, @TraceSesID
,@myTLevel, 1,@Msg,@TraceSource,@TraceCategory
RAISERROR (@Msg, 16, 1)
SET @WorstError =
CASE WHEN @WorstError < @myError
THEN
@MyError ELSE @WorstError
END
GOTO GETOUT
END
END
-----------------------------------------------------------------
-----------------------------------------------------------------
-- Wrap Up the Job
GETOUT:
SET @Msg =
CONVERT(VARCHAR, @NumDelSes)
+ '|'
+ CONVERT(VARCHAR,
@NumDelTrc) + '|'
+ CONVERT(VARCHAR,
@NumDelMsr)
EXEC usp_AppSession_End @TraceSesID
, 'AppSession|AppSessionTrace|AppSessionMeasurement'
, @Msg
, '|'
RETURN @WorstError
GO
There are three nearly identical sections that
do the bulk of the work of purging old rows in AppSession,
AppSessionTrace, and AppSessionMeasurement. Normally all three
tables are purged back to the same day. If the caller wishes to
apply different purge criteria to the each table,
usp_AppSession_Purge can be called three times, each call purging
only one of the three tables. I usually purge all three tables at
the same time back to 31 days ago. To accomplish that I put this
batch in a SQL Server Agent Job:
DECLARE @TID int, @RC int
SET NOCOUNT ON
exec @RC = dbo.usp_AppSession_Purge -3, 1, 1, 1, 3
, 31, @TID OUTPUT
exec usp_AppSession_Rpt @TID
GO
(Results Truncated on the right)
Ses
App Run
----------- ----------- -------------------------------------------
34 0 2005-01-25 13:01:25.157
Time Msg
------------ -------------------------------------------------------
13:01:25.157 PurgeSes=1 PurgeTrc=1 PurgeMsr=1 DelTrcLev=3 DaysAgo=31
13:01:25.157 Deleted 12323 rows from AppSessionTrace.
13:01:25.157 Deleted 1239 rows from AppSessionMeasurement
13:01:25.157 Deleted 332 rows from AppSession.
ParameterName Measurement
-------------------------------- -----------------------------------
AppSession 332.0
AppSessionMeasurement 1239.0
AppSessionTrace 12323.0
One group of statements that is notably absent
from usp_AppSession_Purge is transaction control statements such as
BEGIN TRAN and COMMIT TRAN. In part this is due to the nature of
purging. If a purge fails partially do you really care about
backing out the rest of the purge? I dont. So I just leave out the
transaction control. The lack of transaction statements also works
in favor of storing the trace statements and measurements in a
table. If there were transaction statements, they might remove rows
written to any of the AppSession* tables.
Usp_AppSession_Purge is a good example of how
to write code that uses the AppSession tables. It calls
usp_AppSession_SQLJOB, usp_AppSession_Trace and usp_AppSession_End
in the course of performing its work. This shows how as a library
of procedures is developed, the job of writing additional robust and
opaque procedures becomes easier.
This issue wraps up the theme of tracing and
logging of messages. The next theme is "T‑SQL Coding Style". That
is, how to best write T-SQL code so that it's readable and easily
modified. As always, if you have comments about the
newsletter and in particular, the new direction that it's taking, please let me know at
coding-in-sql@novicksoftware.com.
Transact-SQL User-Defined Functions has been published! Take a look at it
now!
Do you have an interesting stored procedure, user-defined-function,
or other sample code? If you'd like to share them please send
them in an email to:
coding-in-sql@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andy
Andrew Novick
|
|
