none
SQL Maintenance Plans Jobs RRS feed

  • Question

  • Hello All - this week I've inherited serveral SQL2008R2 servers (and I'm certainly no DBA). I've checked the disk space (down to the final .5GBs) on one of the severs and found there was no cleanup job created to delete the old bak files.

    I've created a new job to delete everything older than 2 weeks. If I run the job from the Maintenance Plan GUI nothing happens, the files are not deleted. I've copied the output from the view T-SQL and ran this from a new Query and this works - the BAK files are deleted.

    Question is, am I using different permissions between the maintenance plans and running this from a new query? I've checked the SQL Server Services and these have been set to run as Networkservice on the local server.

    And how to I get this working to delete from the scheduled maintenance tasks?

    Any help would be appreciated.

    Regards

    Simon

    Friday, August 24, 2012 12:09 PM

Answers

  • Hi,

    Have you checked SQL Server Agent service account?

    Check that its account really has delete rights on the backup folder


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, August 24, 2012 5:34 PM

All replies

  • Hi,

    Have you checked SQL Server Agent service account?

    Check that its account really has delete rights on the backup folder


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, August 24, 2012 5:34 PM
  • Hello Sebastian, the account is using the Built-In account Network Service. I've applied the correct permissions for this directory but unfortunately this does not delete the file stills?

    Regards

    Simon

    Monday, August 27, 2012 10:21 AM
  • Hi,

    Try to schedule a maintaince clean up task and check the box which is written as include first level sub-folders...might be you have taken backups in folder according to databases...and in addition check out that you have mentioned the extension name as per the backups eg:- for transcation log use 'trn' and for backups use 'bak'...

    Hope this helps.

    Regards,

    Ashish



    • Proposed as answer by Ashish_DbaSQL Wednesday, August 29, 2012 7:14 AM
    • Edited by Ashish_DbaSQL Thursday, August 30, 2012 5:26 AM
    • Marked as answer by amber zhangModerator Friday, August 31, 2012 7:39 AM
    • Unmarked as answer by SartyCH Monday, September 3, 2012 10:27 AM
    Wednesday, August 29, 2012 7:14 AM
  • Hi,

    Try to schedule a maintaince clean up task and check the box which is written as include first level sub-folders...might be you have taken backups in folder according to databases...and in addition check out that you have mentioned the extension name as per the backups eg:- fro transcation log use '.trn' and for backups use '.bak'...

    Hope this helps.

    Regards,

    Ashish

    No - you do not want .trn or .bak - if you put the '.' in there it will not work, which is probably what is happening here.  Make sure you have only 'trn' or 'bak' - or the extension you used for your backups.

    Jeff Williams

    Thursday, August 30, 2012 1:52 AM
  • Hi,

    Try to schedule a maintaince clean up task and check the box which is written as include first level sub-folders...might be you have taken backups in folder according to databases...and in addition check out that you have mentioned the extension name as per the backups eg:- fro transcation log use '.trn' and for backups use '.bak'...

    Hope this helps.

    Regards,

    Ashish

    No - you do not want .trn or .bak - if you put the '.' in there it will not work, which is probably what is happening here.  Make sure you have only 'trn' or 'bak' - or the extension you used for your backups.

    Jeff Williams

    Sir,

    Its 1 and the same i was jst telng to add extention as which file to be deleted main issue was to schedule the backup

    Regards,

    Ashish

    • Marked as answer by amber zhangModerator Friday, August 31, 2012 7:39 AM
    • Unmarked as answer by SartyCH Monday, September 3, 2012 10:28 AM
    Thursday, August 30, 2012 5:25 AM
  • The problem was with the service account running on the maintaince job. I've changed this now to the DB owner and this works.

    Thanks All

    Monday, September 3, 2012 10:29 AM