August 31, 2009 at 6:08 pm
I have what I think is a stupid question, but it's driving me crazy. I'm trying to create a weekly job that will get rid of the C2 audit files in my d:\mssql.1\mssql\data directory, but am having trouble getting it to work. It's fairly simple:
DECLARE @expiration datetime
DECLARE @today datetime
SET @today = getdate()
SET @expiration = dateadd(day,-15,@today)
EXECUTE master.dbo.xp_delete_file 1,N'D:\MSSQL.1\MSSQL\Data',N'trc',@expiration,1
From what I know, this should remove any files with the 'trc' extension older than 2 weeks. I'm guessing that the files 'are still in use' and can't be deleted, am I going about this all wrong?
TIA
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
August 31, 2009 at 10:06 pm
xp_delete_file has some strange restrictions. I think it only deletes certain types of files, regardless of extension.
You ought to script this with filesystemobject/VBScript or Powershell
September 1, 2009 at 8:39 am
Thanks Steve, that makes sense.
So next newb question: has someone done this with Powershell/VBScript?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
September 6, 2009 at 12:27 pm
Have a look at Microsofts' LogParser.
It works like charm.
One line of code will generate a text file with CMD extension, composed of DEL lines with file names
The other will execute this CMD.
Another way to do that is to use ROBOCOPY with PURGE by date parameter
September 8, 2009 at 5:04 am
A bit off-topic, but I am interested in knowing what benefits you see in C2 logging, and who in your organisation is requesting that you do this.
The DOD TCSEC standard that formulated the various security levels became officially obsolete over a decade ago, being replaced by the Common Criteria standard. The C2 definition crosses a number of CC boundaries, and does not fully satisfy any of the CC categories.
Many security professionals now only have a historical interest, if any, in the TCSEC standard. Hence my interest in why you are using this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply