Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Help Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
Hi ,


I am trying to delete files in a folder except the last created file in the folder.


can you please help me with the logic.


The files in the folder are sql server audit files with the extension .sqlaudit


I cannot choose the files based on date because if the auditing data is not present on todays date . the old files can be deleted.


I am looking for a logic which should look at the files in the folder and delete all the files in that folder except the last created file in that folder.


I am using SQL Server 2008 R2 Enterprise edition RTM 64 bit on windows server 2008 R2 Enterprise edition sp1 64 bit


Thank You,
Post #1502205
Posted Monday, October 7, 2013 9:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
I am not clear with what the criteria you are using for deleting. Maybe an example could help.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1502215
Posted Monday, October 7, 2013 9:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
Hi,

Example: Audit_DBA_991AEE46-71F7-470D-B63C-2EC208FD3303_0_130126053692760000.sqlaudit

Auditing file looks like this. when i go to folder i could see date and time the file was modified.

My auditing files doesn't contain the datetime. i have to check in the folder (M:\Auditing\DBA ) containing the files for the date and time.

I have to delete the files in the folder name M:\Auditing\DBA to save the disk space.


Thank You,
Post #1502229
Posted Monday, October 7, 2013 9:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:02 PM
Points: 33,062, Visits: 15,176
I might look through our scripts for something similar: http://www.sqlservercentral.com/search/?q=delete+old+backups&t=s&sort=relevance

Powershell is how I'd probably do this now, though VBScript works well. You can easily manipulate files and delete all but the most recent.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502237
Posted Monday, October 7, 2013 10:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
Does your instance of SQL Server have xp_cmdshell enabled? If so you can capture the result of the DOS command "DIR" into a table variable containing the date and time. Here is a snippet of code that I use for looking for database backups using this method:

set @FullyQualifiedPath = @BackupPath + '\' + @Database_Name
set @DirectoryCmd = 'dir /b /o:-d ' + @FullyQualifiedPath + '\*.bak'

delete from @DatabaseBackups

insert into @DatabaseBackups (BackupFileName)
exec xp_cmdshell @DirectoryCmd

delete from @DatabaseBackups
where BackupFileName is null or
charindex(@TodayDate, BackupFileName) = 0



Here is the table definition for @DatabaseBackups:

declare @DatabaseBackups table (database_id    int
,BackupFileName varchar(max))


This gives you an idea what you can do. You can capture the date/time of the files then process what ever you want.

Hope this helps.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1502244
Posted Monday, October 7, 2013 11:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:39 AM
Points: 202, Visits: 1,047
Hi Steve,

I don't know Powershell.

Thank You
Post #1502284
Posted Wednesday, October 16, 2013 5:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:02 PM
Points: 33,062, Visits: 15,176
sql2k8 (10/7/2013)
Hi Steve,

I don't know Powershell.

Thank You


This is a good chance to learn. It's not terribly difficult and you have a specific project here to work on. Do a little research and googling.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1505446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse