xp_delete_file not deleting files on share drive

  • Hello,

    xp_delete_file not deleting files on share drive. is there any permission to be given?

    Regards
    Durai Nagarajan

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

    Manu

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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.

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

    Regards
    Durai Nagarajan

  • 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

  • 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

  • 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.

  • 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

  • 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.

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

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