Automatic removal of old backup files fails

[moorejp]

I've set up a SQL 7.0 maintenance plan to complete full backups on certain databases and trans logs each night. The job is supposed to delete files older than two days, but SQL is often failing to do so... and I can find no clear pattern to the failures.

I first had this split into two jobs: One for trans log backups and the other for db backups. The trans log job seemed to delete old files the majority of the time while the full db backup would seldom do so. The fact that it doesn't always fail is even more frustrating than if the failures were consistent.

I deleted both jobs and created new ones, but had the same result. I set up a single job last Friday to do both types of backups, but absolutely no files were deleted over the weekend. In fact, I ended up with several backup failures last night due to lack of disc space (meaning I have to start pushing even harder to get more storage on that server ASAP).

Any answers, ideas, and guesses are greatly appreciated.

John

 

[cdukes]

We had the same problem with SqlServer 6.5 a long time ago. We narrowed it down to permissions. The backups are created by the SQLAgent running on the Server. SQLAgent is logged into the machine with a certain NT username / password. by default this will be sqlexec.

Although SqlExec might have permission to write the backups, it might not have permission to delete them.

A few questions:
Are the backups on a local drive or networked drive.
Where the logs backed up to the same directory / drive as the db.
What are the permissions on the backup directories and files.
What is SQLAgent running as ( System Account / Other Account).
Has whoever SQLAgent is running as full permissions to all directories etc.


Hope this helps,

Chris Dukes