SQL maintenance plan on Primary and secondary server

Question

We have setup Hi Availability group on SQL 2012.

As usual i have created backup maintenance plan in Primary and secondary server with 2 tasks i.e

• Backup maintenance Task
• Maintenance cleanup task

Then i have created Job and selected given maintenance plan.

After many days when i again looked up, i saw that jobs are failing from many days on these servers. sometimes it says about file not found with xp_delete_file().

I am not sure whether continuing backup strategy as i was doing in  SQL2008 is right way or not when working with High Availability group

One thing which i noticed is that backup is running fine when it became secondary server, but i still need to confirm that.

Any suggestions/views

---

Technical User

Answer 1

I'm not sure what's causing your issue as I haven't used maintenance plans in many years however I would suggest looking at Ola Hallengren's maintenance (backup) solution, which works very well with AlwaysOn.

https://ola.hallengren.com/sql-server-backup.html

If you're not seeing backups take place as expected you might need to set your backup preferences/priority. See here for some details

http://blogs.msdn.com/b/sqlgardner/archive/2012/07/21/sql-2012-alwayson-and-backups-part-2-configuring-backup-preferences-and-automating-backups.aspx

Answer 2

Hi Manish,

Could you please help to post the full error message about xp_delete_file()?

I suspect that you save the backup files on a network shared folder, if so, please verify the SQL Server Agent account has permission to access the share and it has permission to perform the delete operation. For more details, please review this similar thread.

Also please note that the Maintenance Cleanup task does not automatically delete files in the subfolders of the specified directory. If you want to delete files in first-level subfolders, you must select Include first-level subfolders. For more details, please review this article.



Thanks,
Lydia Zhang

---

Lydia Zhang
TechNet Community Support