August 22, 2006 at 4:40 am
Hi all,
I would like to write a stored procedure that starts automatically when SQL Server starts, and that logs certain events. I log to a trace file, and I enabled the failover file option:
EXEC @rc = sp_trace_create @traceid OUTPUT
,@options = 2 -- TRACE_FILE_ROLLOVER
,@tracefile = @tracefile
,@maxfilesize = @maxfilesize
,@stoptime = NULL
Then I want to automatically read those files. This works fine with:
SELECT *
FROM ::fn_trace_gettable('D:\Microsoft SQL Server\MSSQL\Audit\LoginAudit.trc', 1) AS T
I have written a loop to read all trace files. However, I run into trouble when I reach the last trace file, which is still in use; I get an error:
Server: Msg 568, Level 16, State 10, Line 1
Server encountered an error 'D:\Microsoft SQL Server\MSSQL\Audit\LoginAudit_2.trc'.
Is there a way that I can first check the 'in use' attribute of this file?
I don't run SQL 2005 with .net on this box!
Thanks,
Jan
August 23, 2006 at 9:11 am
All,
This is the solution I implemented. It can probably be done more simple. I have one stored procedure that logs all security events. It runs when the server is started. It creates logfiles for 500 MB and when full starts another one. Then I have this stored procedure that reads them. I avoided the fact that the last one is in use, by simply not reading in the last one if the trace is running;
CREATE PROCEDURE spd_ImportTraceFiles
@sTraceFile varchar(4000)-- name of initial trace file
AS
DECLARE @nTraceFiles int
DECLARE @sSQL varchar(8000)
DECLARE @bIsRunning smallint
DECLARE @sPath varchar(255)
DECLARE @sFirstFile varchar(255)
DECLARE @sFileName varchar(255)
-- get the first import file number & the number of tracefiles
-- Trace is not running yet, so all files can be read
EXEC master..xp_sprintf @sSQL OUTPUT, 'DIR "%s*.trc" /OD /B', @sTraceFile
-- Parse the path where the files exist
SELECT @sPath = SUBSTRING(@sTraceFile, 1, DATALENGTH(@sTraceFile) - CHARINDEX('\',REVERSE(@sTraceFile))) + '\'
SELECT @sFileName = RIGHT(@sTraceFile,CHARINDEX('\',REVERSE(@sTraceFile))-1)
IF EXISTS( SELECT *
FROM tempdb..sysobjects
WHERE ID = OBJECT_ID('tempdb..#tmpFiles'))
DROP TABLE #tmpFiles
CREATE TABLE #tmpFiles( f_id int IDENTITY, [filename] varchar(255))
INSERT #tmpFiles
EXEC master..xp_cmdshell @sSQL
-- read all but the last file
SELECT @nTraceFiles = COUNT(*)
FROM #tmpFiles WHERE [filename] LIKE @sFileName + '%'
-- find out the first file
SELECT @sFirstFile = @sPath +
(SELECT [filename]
FROM #tmpFiles
INNER JOIN (SELECT MIN(f_id) AS f_id
FROM #tmpFiles
WHERE [filename] LIKE @sFileName + '%') [first]
ON #tmpFiles.f_id = [first].f_id)
-- Is the trace currently running? Then don't import the last file
IF EXISTS( SELECT * FROM ::fn_trace_getinfo(default)
WHERE property = 2
AND value = @sTraceFile)
SELECT @bIsRunning = 1
ELSE
SELECT @bIsRunning = 0
-- Import the logs
SELECT @nTraceFiles = @nTraceFiles - @bIsRunning -- substract 1 if running
IF @nTraceFiles > 0
BEGIN
INSERT AuditLog
SELECT *
FROM ::fn_trace_gettable(@sFirstFile,@nTraceFiles )
-- delete the log files that where imported
-- loop through all files to be imported. In @nTraceFiles the last file is substracted if running.
DECLARE curFiles CURSOR
READ_ONLY FOR
SELECT [filename]
FROM #tmpFiles
WHERE [filename] LIKE @sFileName + '%'
AND f_id <= @nTraceFiles
OPEN curFiles
FETCH NEXT FROM curFiles INTO @sFileName
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
-- EXEC master..xp_fileexist
EXEC master..xp_sprintf @sSQL OUTPUT, 'DEL /Q "%s%s"', @sPath, @sFileName
EXEC master..xp_cmdshell @sSQL
END
FETCH NEXT FROM curFiles INTO @sFileName
END
CLOSE curFiles
DEALLOCATE curFiles
END
DROP TABLE #tmpFiles
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply