Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2007-02-06 : 10:51:33
|
| Hi,I have a Database Maintenance Plan "DB PLAN1" set up with a Complete Backup(Backup file extension as "BAK") and a Transaction Log Backup (Backup file extension as "TRN") to daily at 1:00AM, and Remove files older than 1 week for both of them. After it was running for 2,3 weeks, both .BAK files and .TRN files are created by the backup plan, and the .BAK files over a week were deleted. But the .TRN files were NOT deleted since the backup plan was started. I also find in the Jobs of SQL Server Agent, the Status of "Transaction Log Backup Job for DB Maintenance Plan 'DB PLAN1'" is Failed every time at 1:00AM.Can some one tell me why? and how to modify my backup plan.Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-06 : 11:21:22
|
Probably there are databases with Recovery Model = SIMPLE; SQL Server cannot make a TLog backup of these, and that causes the job to fail. (Might also be that a database is set to ReadOnly, OffLine etc.)If you have a single maintenance plan for all database that will be the case (because master cannot be set to FULL).Probably best you check that all the databases that SHOULD have TLog backups have - i.e. the Main Plan didn't get to Master and then give up on all the rest of the Tlog backups!And if that's the case you could have two plans: One for SYSTEM database, the other for USER databases.But if you have a mixture of SIMPLE and FULL Recovery Model in your User Databases then you will not be able to use a single maintenance plan to "backup all my databases" - and you will then be at risk whenever you add a new database if you forget to add it to the plan  When we got to this point we wrote our own maintenance stored procedures that just backup everything and don't get fooled by Full/Simple, databases set to ReadOnly, OffLine etc.Kristen |
 |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2007-02-06 : 17:07:10
|
| Thanks, Kristen,Yes, I do have 2 plans, one for System DBs, one for other 20 User DBs.All 20 DBs have been setup to "FULL" for Recovery Model in the property for each of them. The problem is that I can get the .TRN backup files each time after the backup is ruuning, but the old .TRN files over 1 week was not deleted.Thank you for your help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 03:50:54
|
| Yeah, I've had that with the maintenance plans. Perhaps I was wrong to assume it was the non-Full recovery model ones then!Either way I decided the plans didn't do what I wanted and wrote my own gear instead.Kristen |
|
|
|
|
|
|
|
|
.TRN files were not deleted