Backups on User databases not deleting old backup files

Question

Post reply

Backups on User databases not deleting old backup files

kwitzell

SSCrazy

Points: 2470
More actions
August 10, 2004 at 9:28 am

#108681

I am having a problem with one of my SQL Servers, old backup datafiles and T-logs for my user databases are not being deleted. The datafile backups for the system databases are being deleted. The only errors in the logs that I can find deal with a write problem to the backup disk. This is understandable since old backup files are filling the disk and I have to delete them manually! I have a number of SQL Servers and this is the only one I have encoutered this problem on. Has any one had any experience with this problem and what is the fix? Any help will be greatly apprectiated.
Thanks!
Ken

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

What do you mean by deleted?

Do you have the backup jobs set for overwrite?

Do you have the backup files set to expire?

-SQLBill

Biffa SSC Veteran Points: 210 More actions · Answer 2

I'd say he has set the backup plan to only keep X days of backups, perhaps one day or two days, and that its not deleting the backups over X days old.

To the orginal poster, check the eventlog for any events that indicate it wasn't able to delete the old backup. Does the whole maintenance plan run through to the end or does it fail somewhere? It could be that its failing somewhere else in the plan and not getting to the delete phase, perhaps on a large db backup that doesn't have space to backup?

I have seen this before, but other than making sure you are running the latest SP for SQL, and making sure the account the Agent runs under has the correct permissions to the directory, I don't have a quick fix for you. Sorry.

Baggy SSChasing Mays Points: 649 More actions · Answer 3

I believe the delete part of a plan (assuming you ahve configured for delete afetr X days etc) only kicks in after the backup part. This means that if there is not enough space for the backup, then the job errors and stops thus not doing the delete part. You initially need to get to a clean state manually by deleting old files so there is enough space to do backup. You in affect need enough space avaialable after the auto delete to allow a backup to occur so at least space for two copies of each database.

Hope this is of some help.

Rick.

mastroine SSC Veteran Points: 224 More actions · Answer 4

I just sent this email to my manager the other day.

We were having similar problems and the things below helped me....

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

I was checking the logs on SQL and apparently the backup drive is full.

Only a couple of the databases have actually successfully backed up.

It appears that the deleting of old transaction logs is the problem. It is not deleting old transaction logs even though it’s scheduled in the DB maintenance plan.

Apparently, this is a bug in SQL Server 2000…

http://support.microsoft.com/default.aspx?scid=kb;en-us;303292

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=120549#bm120558

http://www.mcse.ms/message862355.html - they reference MSDE, but make a comment that SQL 2000 has the same problem

Initially, I split up the maintenance plan to not include transaction log backups on the system databases.

If that doesn’t help, I will go through and set the recovery backup options to FULL.

------

Hope this gives you a couple ideas.

Matt

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Biffa,

You assume kwitzell has set an expire time frame. But he/she may not have done so. That's the purpose of my questions.

There is an issue with expire by settings. It works if you do separate backups. However, if you append - expire won't work. The expire option doesn't go by the date/time the BACKUP was made, it uses the Operating Systems file date/time. So whenever an append happens the file date/time is changed and expire never happens.

But if kwitzell isn't using the expire option, then that's not the issue.

Once kwitzell lets us know how the backups are being done (append, overwrite, completely new files) and how they are supposed to be deleted (by SQL Server expire option or by operating system function), then we can provide good solutions. Otherwise, we are all just guessing at what might be the issue.

-SQLBill

kwitzell SSCrazy Points: 2470 More actions · Answer 6

First of all I want to Thank all of you for your responses. I am indeed using the keep x number (2 days actually) of backups. All user databases are set to Full recovery model, the system databases are set to simple and are backed up in a seperate maintence plan. The backups do complete as required but are not deleted when they are older than 2 days. As a result the disk fills and backups stop at that point. I have deleted them manually since I first noticed this problem. I removed all backup files and started with clean disk and have also recreated the maintenance plan. None of these actions made any difference. One thing I have noticed is the backups for the system databases do expire after the required time and will not accumlulate. The User databases are the only ones that will not expire the backup files. I have multiple SQL Servers and this is the only one I have a problem with. This server is a 2003 server could that have something to do with it? Thanks again for your help!

Ken