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 ««12

Script to Delete Reports Older than a Certain Number of Days Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 6, 2014 9:10 AM
Points: 10, Visits: 19
I would like to thank each one of you for your replies and suggestions.

No, this is not a test question. We have jobs that are supposed to delete files older than a certain number of days. For some reasons, the job is not working on our text files.

Thank you,
Post #1514297
Posted Thursday, November 14, 2013 9:42 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
hisakimatama (11/8/2013)
Hm. I could be wrong on this, but I believe xp_delete_file will only work on SQL Server backup and report files; that is, files ending in .trn, .bak, and .rpt. It looks like you're trying to delete .txt files with it, which shouldn't work. You may have to resort to using a PowerShell script to do this instead.


Simbo_mk (11/14/2013)
I would like to thank each one of you for your replies and suggestions.

No, this is not a test question. We have jobs that are supposed to delete files older than a certain number of days. For some reasons, the job is not working on our text files.

Thank you,


Like hisakimatama said in the second post on this thread (quoted above, as well), xp_delete_file won't delete .txt files. It works only for backup and report files and they have to have the correct embedded header, to boot.

As a bit of a sidebar, this demonstrates that great care and very careful research/testing should be done before using any undocumented stored procedure.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1514360
Posted Thursday, November 14, 2013 2:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 6, 2014 9:10 AM
Points: 10, Visits: 19
Hell again,

1.) I typed the following Powershell script in my job step:

$a = Get-ChildItem "E:\Backups\MaintenancePlanReports" -recurse
foreach($x in $a)
{
$y = ((Get-Date) - $x.CreationTime).Days
if ($y -gt 14 -and $x.PsISContainer -ne $True)
{$x.Delete()}
}

2.) I executed the job

I am getting the following error as a result:

The corresponding line is '{$x.Delete()}'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Delete" with "0" argument(s): "The process cannot access the file 'LogBackup-HourlyRun.txt' because it is being used by another process." '. Process Exit Code -1. The step failed.[/font]


Any suggestions???

Thank you!

Simbo_mk
Post #1514477
Posted Thursday, November 14, 2013 4:25 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 586, Visits: 6,675
Hm. From the error message, it seems someone or something is using the file you're trying to delete. If you accidentally opened it before you deleted it and kept it open (and I know I've done that myself far, far too many times! ), close the file and run the script again. Otherwise, see if someone else has access to the file and opened it for some reason.



-
Post #1514528
Posted Friday, November 15, 2013 12:32 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
Simbo_mk (11/14/2013)
Any suggestions???


Yes... use the DOS command I posted instead.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1514821
Posted Thursday, December 26, 2013 12:41 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
Jeff Moden (11/15/2013)
Simbo_mk (11/14/2013)
Any suggestions???


Yes... use the DOS command I posted instead.


Or do as was suggested earlier and just use the drag and drop feature in the Maintenance Plan.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1526021
Posted Thursday, December 26, 2013 3:02 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
SQLRNNR (12/26/2013)
Jeff Moden (11/15/2013)
Simbo_mk (11/14/2013)
Any suggestions???


Yes... use the DOS command I posted instead.


Or do as was suggested earlier and just use the drag and drop feature in the Maintenance Plan.


If it's the proper file type with the correct header, I agree. If not, then drag'n'drop isn't foing to help here.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526057
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse