xp_delete_file not deleting files on share drive

Question

Post reply

xp_delete_file not deleting files on share drive

durai nagarajan

SSCoach

Points: 15778
More actions
August 22, 2012 at 9:08 am

#260976

Hello,
xp_delete_file not deleting files on share drive. is there any permission to be given?
Regards
Durai Nagarajan

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

I think read/write access to shared location is must.

Manu

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

it has write access as the job is creating the log files backup in the folder which is required.

in which services will the XP_Delete_File runs ,hope that permission might left out.

Regards
Durai Nagarajan

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

You could always switch to a CmdExec step type. Or write a PowerShell script to interact with the file system, which it is more capable of doing than T-SQL.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

EdVassie SSC Guru Points: 60472 More actions · Answer 4

What security context is running the xp_delete_file? Is the shared drive visible to that security context?

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

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

what services will be userd to tun XP_Delete_File. i dont know about that.

opc, i'll try that but how about deleting files on a date range basis.

Regards
Durai Nagarajan

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Make sure that your proxy account has access to the location that you are attempting to delete the files. When you use xp_ functions, it uses the proxy account (and it's inherited security from the domain (AD)) by default.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

durai nagarajan (8/29/2012)
opc, i'll try that but how about deleting files on a date range basis.

A simple chore with PowerShell. Here is a one-line PowerShell command that does the work for you. You can execute the command from a PowerShell step type in a SQL Agent job. Adjust constants to suit...remove the -WhatIf to have it really do the deletion:

Get-ChildItem -Path "E:\Backups\" -Filter "*.bak" |? {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item -WhatIf

To also look in all sub-folders:

Get-ChildItem -Path "E:\Backups\" -Filter "*.bak" -Recurse |? {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item -WhatIf

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Make sure you have the latest SQL 2005 service pack installed.

There was a problem with deleting files in maintanance plans in the early versions of SQL 2005.

durai nagarajan SSCoach Points: 15778 More actions · Answer 9

i never worked in PowerShell do i have to install somehting or am i missing something?

Regards
Durai Nagarajan

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

durai nagarajan (8/29/2012)
i never worked in PowerShell do i have to install somehting or am i missing something?

PowerShell 2.0 is standard equipment in Windows Server 2008 R2. What are you running? For previous versions install the Windows Management Framework (Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0) for your OS. Note, you may need to apply an OS service pack before you can install the Windows Management Framework.

It is worth getting to know PowerShell. It makes sys admin tasks like this extremely simple and Microsoft is backing the technology. Most of their Server products (e.g. SQL, Exchange, Windows, Active Directory, IIS, etc.) are being shipped with PowerShell management modules. Server Core has no user interface and PowerShell will be how servers are managed there.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

durai nagarajan SSCoach Points: 15778 More actions · Answer 11

thanks opc , we are on windows 2003 server and sql 2005.

hope i have to install powershell on the server to use it.

Regards
Durai Nagarajan

Greg Drake Ten Centuries Points: 1071 More actions · Answer 12

I have had this problem before when I attempted to use a maintenance plan cleanup task to delete perfmon files as they got old. When it didn't work I scripted it out and it used the xp_delete_file function. It turns out that function is designed to only work on SQL Server files like backups.

http://stackoverflow.com/questions/212603/sql-server-xp-delete-file-not-deleting-files

As recommended above, I ended up using a short PowerShell script to do this. In 2008, you can specify an Agent Job Step with a type of "PowerShell", which saves you having to save the script as a file and make the Job Step run as type CmdExec.

This is an example of the script I used.

$dir = "D:\PerfLogs\Baseline\"

$file_filter = "*.blg"

$age_in_days = 30

$files_to_delete = Get-Childitem "$dir$file_filter" | Where {$_.LastWriteTime -le (Get-Date).AddDays(-1 * $age_in_days)}

Remove-Item $files_to_delete

EDIT: Fixed buggy script.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

Greg Drake (8/30/2012)
I have had this problem before when I attempted to use a maintenance plan cleanup task to delete perfmon files as they got old. When it didn't work I scripted it out and it used the xp_delete_file function. It turns out that function is designed to only work on SQL Server files like backups.
http://stackoverflow.com/questions/212603/sql-server-xp-delete-file-not-deleting-files
As recommended above, I ended up using a short PowerShell script to do this. In 2008, you can specify an Agent Job Step with a type of "PowerShell", which saves you having to save the script as a file and make the Job Step run as type CmdExec.
This is an example of the script I used.
$dir = "C:\perflogs\baseline\"
$file_filter = "*.blg"
$age_in_days = 365
$files_to_delete = Get-Childitem "$dir$file_filter" | Where {$_.LastWriteTime -le (Get-Date).AddDays($age_in_days)}
Remove-Item $files_to_delete

Bug in your script?

(Get-Date).AddDays($age_in_days) will add 365 days to today...every file should be older than that 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

db_expert_pradeep SSC Veteran Points: 294 More actions · Answer 14

If xp_delete_file is deleteing file localy and not able to delete on network then sure this is permission issue.

Are you able to see that files?

You can check to give access for a while with adding your self as a administrator on system where files which you want to delete.