Post reply

master.dbo.xp_delete older backup files not deleted

  • February 11, 2015 at 11:09 pm

    #314575

    Hi

    Below Command is working with SSMS without any errors. Management plan job dos not delete older 3 days files by automatically after taking Transaction log backups.

    EXECUTE master.dbo.xp_delete_file 0,N''E:\SQL_DB_Backups\TRNLogs'',N''trn'',N''2015-02-09T11:00:35'',1

    SQL Agent service running under domain account, so this account already added into Local administrators group and also have sufficient privileges.

    Pl. suggest.

    Thanks

    ananda

  • February 12, 2015 at 4:50 am

    #1777186

    I have tried manually run that script but not deleted old files

    declare @DeleteDate nvarchar(50)

    declare @DeleteDateTime datetime

    set @DeleteDateTime = DateAdd(hh, -48, GetDate())

    set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

    --EXECUTE master.dbo.xp_delete_file 0,N'E:\SQL_DB_Backup\Full_Backup',N'bak',@DeleteDate,1

    EXECUTE master.dbo.xp_delete_file 0,N'E:\SQL_DB_Backup\Tlog_Backup\',N'trn',@DeleteDate,1

    rgds

    ananda

  • February 12, 2015 at 7:14 am

    #1777228

    SQL Galaxy (2/11/2015)

    SQL Agent service running under domain account, so this account already added into Local administrators group and also have sufficient privileges.

    Open explorer and check the privileges assigned, just because you put an account in the local admins group doesn't mean it can access all folders on the server. If someone has changed the ACLs then it will fail, check them carefully

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 12, 2015 at 7:15 am

    #1777230

    also, try using the following as the extension

    N'BAK'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 13, 2015 at 12:24 am

    #1777414

    Thank you Perry,

    management plan itself working and deleted old days backup files after adding account in backup folder and given modify privileges.

  • February 13, 2015 at 3:43 am

    #1777456

    SQL Galaxy (2/13/2015)

    after adding account in backup folder and given modify privileges.

    😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 6 posts - 1 through 5 (of 5 total)

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