Backups won't delete

[barbola]

I have a db maint plan for a daily backup, and it is set to remove fields older than 2 days.

This morning there were 3 days in there.

I checked, all the db's are set to full except msdb and master (simple). Not sure if this is the cause? Should i even have these included in the backup?

We upgraded our db on the weekend (Great Plains) so I had disabled this backup plan and re-enabled it on Monday.

b.

 

[ptheriault]

Have you checked the job history? Did the job run this morning?

 

[barbola]

The job? If you mean our backup? Yes, the backup ran last night as it was supposed to, but the 3-day old backups did not delete.

 

[ptheriault]

right click on the maint plan and select view history.
Then click on the '+' next to todays run. It will open up the history for each step of the maint plan. You will be able to see if the step to delete backups ran or not and for how long. It will also tell you why it failed if that step failed.

 

[SQLBill]

Are you appending the backups to the same file on a hard drive? If so, SQL Server can't remove old files - there aren't any. Right click on the file, go to properties and see what the date/time the file was last modified. That is what SQL Server goes by. To get this to work, you must save each of your backups to a separate file.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

SQL bill, no they are not appending.

Ptheriault - I will check this.

thanks.

 

[barbola]

Okay I am viewing the history, but I don't see a + sign anywhere.

I see the jobs by date, and it shows a couple of db's being deleted, and all the backup activity, but I don't see any errors or anything to show that anything failed?

 

[ptheriault]

I should have asked if this was 2000 or 2005. If it is 2000 you will need to select the check box to show details. However, if you can see that it deleted backups today and there are no errors... I don't think I would be to answer why it left behind some but delete others. Maybe SQLBill will know why. I have never seen that before.

 

[barbola]

Oh well, I am gone for a few days and made sure the network guys are going to check this...otherwise our drive fills up and data gets corrupt...not good!

 

[u080570]

I am getting the same thing (SQL 2005)....The maintenance plans run fine each night - they do everything except delete old backups and trans backups. There are no error messages, the history says everything ran fine (I have also checked the "Include first-level subfolders" as the help suggested). The only strange thing I see is when I look at the history, it says everything finished successfully, but it only show 3 of my 5 steps (the 2 steps not showing are the clean-up tasks)....it's almost like it's skipping them or something ? I have then linked using the "completion" option...

 

[u080570]

I may have found my answer - further down in the forum, I found that if you have one db set to the simple recovery model, it doesnt actually get backed up in the maintenance plans - causing the plan to "fail" so the cleanup tasks dont run. I searched my dbs and had 1 set to simple. I then checked all my backups and what do ya know, it wasn't getting backed up....I guess this caused my backup task to fail (even though the log says it was successful) so my cleanup tasks were never run....

Great info on this forum!!
MM

 

[SQLBill]

MM,

You can never do a log backup on a db that is in SIMPLE mode AND you can never do a log backup on the Master db (per BOL). You can set other system dbs to FULL and then do log backups, but this won't work for the Master db.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

SQL Bill, I don't understand. If we have to backup the master db every night, and it's set to simple, why does this cause ALL the other db's that are set to full, to not have their old backup files removed?

 

[SQLBill]

Barbola,

I never said that was the cause of your issue. MM (u080570) interupted your thread and I was responding to that question.

However, if one step fails, then all the following steps fail. So, if your maintenance plan does tlog backups of all databases and then removes old files...it will fail in the removal when it fails on backing up the Master log. But, you need to check your steps in the job...if it is trying to backup the Master log before it has done ALL the other dbs, then it will not backup the dbs that follow it.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

ohhhh ok thanks. I think it is working now.