Maintenance plan was not deleting the files

Question

Post reply

Maintenance plan was not deleting the files

Guras

SSCertifiable

Points: 6518
More actions
June 14, 2019 at 8:33 pm

#3653969

I have two instances of SQL servers running from a machine. SqlServer ( default instance) is version 2012 and sqlserver\Instance1 is version 2016 . Databases from both the instances are backed up to the same drive W:\Backups.
I created a maintenance plan from Sqlserver ( default instance) to delete any bak files in the share W:\Backups . However it was not deleting any backup files of the database sqlserver\Instance1. Once I created the maintenance plan from the sqlserver\Instance1, the files got deleted. But I want to understand what prevented the sqlserver ( default) maintenance plan from deleting the sqlserver\Instance1 backup files though they are in the same location and both the jobs are run by the same service account.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

The stored procedure that is used by the maintenance task checks the file(s) to be deleted are from the instance where the job is running - and validates the files are actually SQL Server backup files associated with that instance.

If you want a single system to manage all files - you can use a Powershell script to delete files older than xx days. That scripts could then be scheduled in any instance on the machine and set to run using the Powershell subsystem (with appropriate proxy account).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I'm going to suggest that you DON'T make such an over-arching delete system. There may be a need for different instances/systems to do the deletes of old backup files on different schedules for different needs.

Also, if the system that executes the over-arching delete system fails, you might be screwed for disk space in an urgent manner. Each system/instance should take care of it's own backups and backup files.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)