xp_delete_file issues
-
Hi all,
Does anyone know how xp_delete_file works?
I tried to delete files older then 48 hours but nothing would be deleted.
This is what i have.
DECLARE @ThreeHoursAgo VARCHAR(50)
SET @ThreeHoursAgo = CAST(DATEADD(hh, -3, GETDATE()) AS VARCHAR)
EXEC master.sys.xp_delete_file 0,N'\\sql03\W$\Backups\ESSBASE',N'bak',@ThreeHoursAgo
After executing this, i get command completed successfully, but no files get deleted.
I tried everything as other post suggested:
1) capital letters in 'BAK'
2) put 1 instead of 0 in the first parameter
3) specify local path like W:\Backups\ESSBASE'
4) Add and remove the last parameter for option of searching in subfolders...no luck - no files get deleted...
i then just tried to execute the following:
EXEC master.sys.xp_delete_file
1, -- delete files
N'W:\Backups\ESSBASE', -- full path to the main directory
N'.bak', -- file extension
N'01/30/2008 09:30:00.000', -- delete files created before this timestamp
1
same thing...command completed successfully, but no files were deleted.
HELP! PLEASE!
is there a special format for a date i have to use? Anyone?
thank you
-
Your script ran perfectly fine for me. Is the BAK file in the target directory authentic or did you just rename a text file or something?
I have run into a problem testing where I had to bring in an actual backup before this command would work. I tested this with your script and it would only delete the backup created by SQL server.
This function looks at more than the file extension before deleting.
-
check your date format. I you run the code
select
CAST(DATEADD(hh, -3, GETDATE()) AS VARCHAR)
you get
Jan 30 2008 9:42AM
When you manually run it above you are using a different formatted date.
-
Thank you for your reply!
Nothing gets deleted in both cases regardless date format i am using...
So my question was actually: what format should the date be?
in both cases i get command executed successfully, but no files deleted.
Help:)
-
check your date format. I you run the code
select
CAST(DATEADD(hh, -3, GETDATE()) AS VARCHAR)
you get
Jan 30 2008 9:42AM
When you manually run it above you are using a different formatted date.
I ran here code exactly as she has it and it worked. The problem lies elsewhere. Is the backup a valid sql backup and have you tried moving the file to a local directory and testing it?
-
And to clarifiy when I said it worked. I mean the backup file was deleted successfully.
-
Can you provide a screen print of a DIR of the directory you are trying to delete files in?
-
Here is the print screen:
-
And i think i renamed the files (not the extensions though) because i was testing the other job based on filenames. 🙂
-
Unfortunately, I can't see the screen print.
-
how about now?
-
OK everyone!
the problem was my files were not a valid sql backup files.
As soon as i tested it on valid sql backups it worked.
P.S. I believe the date parameter is Date modified, not date created as i thought.
When i copied some valid backup files date created changed to the date and time i copied them, but they still get deleted when i said delete files older than 17 hours (when they were modified).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply