September 20, 2017 at 1:27 pm
Does anybody have an already packaged method to clean up trace files that have reached a size threshold? I have a trace set to use 2 roll over files and do a max of 500 megs per file. However, once the files fill up, the trace is turning itself off. I then have to go back in and create the trace again and it keeps working till the files fill up again. I don't get why it won't just keep running and overwrite the files.... 🙁
September 20, 2017 at 1:59 pm
amy26 - Wednesday, September 20, 2017 1:27 PMDoes anybody have an already packaged method to clean up trace files that have reached a size threshold? I have a trace set to use 2 roll over files and do a max of 500 megs per file. However, once the files fill up, the trace is turning itself off. I then have to go back in and create the trace again and it keeps working till the files fill up again. I don't get why it won't just keep running and overwrite the files.... 🙁
I kind of remember this from before. In the previous post you said you have the second parameter set to 6? The options argument of sp_trace_create? If that's what you have then the options should be set to 2 to enable rollover files. For example, if everything uses variables other than enabling the rollover, it would be something like:
sp_trace_create @TraceID output, 2, @OutputFileName, @MaxFileSize, @EndTime, @FileCount
When it's running you can also query sys.traces and check the is_rollover column to see if it's enabled.
Sue
September 20, 2017 at 4:22 pm
I had to do something similar. I created a job with 2 steps, first step was to rename, load data from existing trace and second step was to run the trace with filters. However it doesn't go back and delete older files, that should be simple enough script i think.See if this helps.
This code is used to load data into trace table, rename old trace files
CREATE PROCEDURE [dbo].[Control_AuditTrace]
AS
DECLARE @tcid INT
DECLARE @format_datetime VARCHAR(MAX)
DECLARE @file VARCHAR(MAX)
DECLARE @file2 VARCHAR(MAX)
DECLARE @cmd VARCHAR(8000)
DECLARE @cmd2 VARCHAR(8000)
DECLARE @file_path NVARCHAR(MAX)
CREATE TABLE #file_list
(
fl_name VARCHAR(500) NULL
)
SET @format_datetime = CONVERT(VARCHAR(10), GETDATE(), 112)
+ REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108), ':', '')
SET @file = 'L:\SQLAudit_Trace\SQLAuditTRC_' + REPLACE(@@servername, '\',
'_') + '.trc'
SET @file_path = 'L:\SQLAudit_Trace'
SET @cmd2 = 'EXEC master.dbo.xp_cmdshell ''dir "' + @file_path + '"/b /s'''
IF EXISTS ( SELECT *
FROM sys.traces
WHERE path LIKE '%L:\SQLAudit_Trace%' )
BEGIN
SELECT @tcid = id
FROM sys.traces
WHERE path LIKE '%L:\SQLAudit_Trace%'
--select @tcid
PRINT 'About to stop and disable trace'
EXEC sp_trace_setstatus @tcid, 0
EXEC sp_trace_setstatus @tcid, 2
PRINT ' About to load trace data in the table'
INSERT INTO dbo.AuditSQLAccess
SELECT
--*
EventClass ,
ApplicationName ,
ClientProcessID ,
DatabaseID ,
DatabaseName ,
EventSequence ,
GroupID ,
HostName ,
IntegerData2 ,
IsSystem ,
LineNumber ,
LoginName ,
LoginSid ,
NTDomainName ,
NTUserName ,
NestLevel ,
Offset ,
RequestID ,
SPID ,
ServerName ,
SessionLoginName ,
StartTime ,
State ,
TextData ,
TransactionID ,
XactSequence ,
BinaryData ,
ObjectID ,
ObjectName ,
ObjectType ,
SourceDatabaseID ,
@file
FROM FN_TRACE_GETTABLE(@file, DEFAULT)
PRINT 'Renaming Existing trace file'
SET @file2 = 'SQLAuditTRC_' + REPLACE(@@servername, '\', '_')+ '_' + @format_datetime + '.trc'
-- change trace file name
SELECT @cmd = 'RENAME ' + @file + ' ' + @file2
EXEC master..xp_cmdshell @cmd
END
INSERT INTO #file_list
EXEC ( @cmd2
)
-- This condition was added to see if a trace was abrutply stopped
IF EXISTS ( SELECT *
FROM #file_list
WHERE fl_name = @file )
--AND NOT EXISTS (select * from AuditSQLAccess where filepath =@file )
BEGIN
PRINT 'Second Batch: About to load trace data in the table'
INSERT INTO dbo.AuditSQLAccess
SELECT
--*
EventClass ,
ApplicationName ,
ClientProcessID ,
DatabaseID ,
DatabaseName ,
EventSequence ,
GroupID ,
HostName ,
IntegerData2 ,
IsSystem ,
LineNumber ,
LoginName ,
LoginSid ,
NTDomainName ,
NTUserName ,
NestLevel ,
Offset ,
RequestID ,
SPID ,
ServerName ,
SessionLoginName ,
StartTime ,
State ,
TextData ,
TransactionID ,
XactSequence ,
BinaryData ,
ObjectID ,
ObjectName ,
ObjectType ,
SourceDatabaseID ,
@file
FROM FN_TRACE_GETTABLE(@file, DEFAULT)
PRINT 'Renaming Existing trace file'
SET @file2 = 'SQLAuditTRC_' + REPLACE(@@servername, '\', '_')
+ '_' + @format_datetime + '.trc'
-- change trace file name
SELECT @cmd = 'RENAME ' + @file + ' ' + @file2
SELECT @cmd
EXEC master..xp_cmdshell @cmd
END
ELSE
PRINT ' Didnd''t had to do anything, nothing was running'
This code is to run the trace and rollover to next file when it reaches it limits.
/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 03/17/2016 05:55:47 PM */
/****************************************************/
CREATE procedure [dbo].[Start_AuditTrace]
As
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
DECLARE @format_datetime char(25)
declare @file nvarchar(256)
declare @tracefile nvarchar(256)
SET @format_datetime = CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')
set @file='L:\SQLAudit_Trace\SQLAuditTRC_'+REPLACE(@@servername,'\','_')
set @tracefile=@file
select @tracefile set @maxfilesize = 500
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, @tracefile , @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
-- Modified sproc to add SQL:BatchStarting as it tracks only once
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 64, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 41, @on
exec sp_trace_setevent @TraceID, 13, 49, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 50, @on
exec sp_trace_setevent @TraceID, 13, 66, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 51, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 60, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Repl-LogReader'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'.Net SqlClient Data Provider'
--- @columnid = 1 for Textdata
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%insert bulk%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
September 21, 2017 at 12:38 pm
Ok, so I checked and yea you're right I do have 6 in there. But 6 should still be doing rollover?
If I change it to 2, and I only have 2 rollover files specified.... will it go back to the first file and start overwriting it?
The rollover part is working... but its just turning itself off when it gets to the max rollover and won't just keep overwriting the data.
September 21, 2017 at 1:35 pm
amy26 - Thursday, September 21, 2017 12:38 PMOk, so I checked and yea you're right I do have 6 in there. But 6 should still be doing rollover?
If I change it to 2, and I only have 2 rollover files specified.... will it go back to the first file and start overwriting it?The rollover part is working... but its just turning itself off when it gets to the max rollover and won't just keep overwriting the data.
It's just writing to two files but not really rolling over. And now that I think of it, you have two options enabled - the rollover and the shutdown on error. But shutdown on error is SQL Server itself stopping on error. You would see an error in the SQL Server log when that happens. So yours is acting weird. I'd probably try to set it to two (just enables the rollover) if possible and see if it works. If you have to leave it at 6 with the shutdown on error enabled, check to see if you have enough space where those are writing to store at least three on disk, should be more. If the process can't delete a file because it's in use then it won't retry and it will say on disk. You'd want space to accommodate this.
When working correctly without errors, it only keeps on disk the number of files set for the file count. It would work like this if max file count is 2:
First writes to trace1.trc and when that hits max size then writes trace2.trc then the next would be trace3.trc and trace1 is deleted
Then writes to trace3 and when trace3.trc is full it creates a new trace4.trc and trace2 is deleted, etc.
So you only have two on disk - creates a new file and deletes the oldest and just maintains the max file count. And doesn't shut itself off.
If this never runs correctly and you have to manually delete the files (and then manually start? still seems odd) then I'm not sure if you can use the script that curious_sqldba provided for you. I thought you had mentioned stigs before and I thought that mandated no xp_cmdshell which is what is in that script. Not sure if you can use Powershell as an alternative but that would be another option.
I'd still try to figure out why its not working correctly though - check the windows event logs or maybe set something up with process monitor to see if you can find the issue. I'd also query sys.traces when it's running just to check if the definitions seem correct.
Sue
September 21, 2017 at 1:43 pm
Ok thank you! I changed it to 2 and I put the max file count at 3. I have plenty of room on the drive, so its not a space issue. I have it set to do 500 megs for each file. We'll see if changing it thusly makes a difference.
Thanks again.
September 21, 2017 at 1:53 pm
amy26 - Thursday, September 21, 2017 1:43 PMOk thank you! I changed it to 2 and I put the max file count at 3. I have plenty of room on the drive, so its not a space issue. I have it set to do 500 megs for each file. We'll see if changing it thusly makes a difference.Thanks again.
Post back if you remember to - I'm curious as to if that works or not.
And still wondering about how weird it is being - going to see if I can reproduce the issue.
Sue
September 27, 2017 at 11:22 am
BAH!!!! It stopped again!!! 🙁
September 27, 2017 at 11:28 am
Ok maybe dumb question.... I noticed that the last time the trace wrote to the output file was on 9/24. Our servers are rebooted every Sunday and 9/24 was a Sunday. Would the trace stop due to the server rebooting?
September 27, 2017 at 11:37 am
amy26 - Wednesday, September 27, 2017 11:28 AMOk maybe dumb question.... I noticed that the last time the trace wrote to the output file was on 9/24. Our servers are rebooted every Sunday and 9/24 was a Sunday. Would the trace stop due to the server rebooting?
Yes a reboot would stop the trace.
You can have the trace in a startup procedure if you needed - use sp_procoption to set the stored procedure as a startup procedure and then use sp_configure to make sure the instance is scanning for startup procs with the 'scan for startup procs' setting.
Did it run long enough, create enough files to see if the rest was working okay?
Sue
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply