Backups not deleting and other questions 2

[barbola]

I have hourly transaction logs and daily database backups. I recently did an upgrade to my database application and had changed the databases to simple and then back to full afterward. At this time I also disabled the hourly trx log and forgot to re-enable it until this morning.

Anyway, the backup failed last night because the drive was full. I noticed the daily backups were not being deleted. I checked the db's and all are Full recovery mode.

I'm not sure if it was the msdb which was set to simple? I think it should be though? What about master and model? Should they be full or simple?

Or did the backups not delete because the transaction logs weren't backing up?

thanks.

 

[ptheriault]

Your backups didn't delete because most likely the maint plan didn't get to that step. If you have 5 steps in a maint plan and it fails on step 2 then steps 3, 4 and 5 are never executed. You should also check to see if the account that the SQL agent is running on has access to the file system to delete those files. That will also prevent backups from being deleted. If you search some previous posts here Denny has some very good posts why files fail to delete.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[barbola]

It's not an access issue. It always worked before.

The backup job failed last night because the drive was full. It didn't fail the night before, but it didn't delete old backups either.

The reason it was full is because backups from last week were not deleting, and I don't know why. There is nothing in the log file saying why the backups didn't delete.

Thanks.

 

[Catadmin]

The following is a Wild-Axx-Guess by Catadmin. It does not reflect the opinions of this forum. @=)

It probably is because you switched the DB to Simple mode. Once you did that, it may have lost the "connection" (or history) to the Transaction logs since it thought "Simple = no kept transaction log backups".

Since you've changed the DB back, once you start getting succeeding backups, watch the plan closely. I bet the transaction logs done since today will start getting backed up & deleted. And let us know if it deletes the older (pre-Simple) transaction logs.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[barbola]

Okay but I already deleted all the transaction logs to free up the disk space. :~)

But, my big question is, what recovery mode should master, model, and msdb be? Simple or Full?

thanks
barb

 

[ptheriault]

Here are the default recovery models for Master, Model and msdb.

Master - simple
Model - full
msdb - simple

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[barbola]

Thank you. I will check my trx logs in a couple of days.

barb

 

[Catadmin]

FYI: There is no reason to have anything other than the defaults on the system DBs because most people rarely make changes that affect the system DBs once SQL is completely set up.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[barbola]

OK thanks...duely noted for next servicepack!

barb.

 

[SQLBill]

Remember-you CAN'T do TLog backups of Master, it WILL FAIL. When we do maintenance plans, we create two of them. One is for all databases using SIMPLE recovery mode and one for all using FULL recovery mode. On the plan for SIMPLE, we do not set TLog backups.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

Yes, my maintenance plan didn't change so it's still okay, it was just the recovery mode of the databases.

thanks.

 

[lcolson1959]

You mentioned your backups do not get deleted. Are you using SP1? I noticed a thread somewhere that suggested this is a known bug. If you place your backups in separate directories they won't get deleted. I tried putting them in the same directory and they still are not getting deleted. Just wondering if anyone has any experience with SP1 and this problem.

 

[barbola]

We are on SP4. It looks like they are working/deleting now.

thanks