Old Transaction Logs Not Deleting During Sched. Maint.

Question

Old Transaction Logs Not Deleting During Sched. Maint.

Ken Keith-243697

SSC Enthusiast

Points: 150
More actions
July 13, 2005 at 7:23 am

#109995

SQLServer2000 running on Win2003 Server
Have a scheduled maintenance job for backing up my transaction logs. It appears that the backups are happening but the deletion of trans logs older than 7 days is not.
My scheduled jobs looks like:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 2E5BB620-B469-4B91-87A6-1C0582507F4E -Rpt "E:\SQL_Backups\Confirmit Database Backup6.txt" -DelTxtRpt 7DAYS -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpLog "E:\SQL_TransactionsLogs" -DelBkUps 7DAYS -CrBkSubDir -BkExt "TRN"'
When I look in the log file for the run there is an Exit of Failed at the end which I'm assuming is why the deletion is not happening. However the only "error type" messages I see in the log are:
Backup can not be performed on database 'master'. This sub task is ignored.
Backup can not be performed on database 'msdb'. This sub task is ignored.
Backup can not be performed on database 'Northwind'. This sub task is ignored.
Backup can not be performed on database 'pubs'. This sub task is ignored.
Is this my problem? If so:
1) Where do I look for more info on the actual reason for this failure
2) Why can't the backup performed on these db's
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

If the backup jobs fail, the older files won't be deleted as this is the last task that the jobs attempt. You'll need to find out what's causing the job failures and that should fix your problem.

My hovercraft is full of eels.

sswords SSCrazy Eights Points: 8207 More actions · Answer 2

Another thing. If your databases are set to simple recovery mode transaction log backup are not allowed. Master and msdb are set to simple mode by default when SQL Server is installed. The maintenance plan wizard will allow you to create a transaction log backup job for databases that are set to simple recovery mode, but the job will fail when it attempts to run.

Hope this helps.

My hovercraft is full of eels.

Ken Keith-243697 SSC Enthusiast Points: 150 More actions · Answer 3

Thanks for the info...took a look and the problem seems to be "simple".

As I am indeed a "newbie", what is the downside to me changing those db's from simple to full?

Are we talking just eating up more disk? Any performance considerations?

Thanks again for the help.

sswords SSCrazy Eights Points: 8207 More actions · Answer 4

I personally tend to leave the system databases (master, model, msdb) in simple recovery mode since that's the way they install and then take regular full backups of these. They're all usually small and the backups don't take up a lot of space. I'll usually set the backups to delete after a week or two. We also use a secondary tape backup system which adds a level of redundancy.

User databases are another matter and what recovery mode and how often you backup are for the most part dependant on how much data you can afford to lose. Our typical production backup strategy is to backup user DB's daily after hours and dump (backup) the transaction log hourly. This strategy ensures that we can recover to a point in time keeping data loss at a minimum.

If you're new to SQL Server, you will find a lot of friendly, knowledgable, and helpful people here. I'd also like to suggest that you take a look at the "Backing Up and Restoring Databases" topic in Books On Line which ships will SQL Server. You will find this an excellent resource I hope.

My hovercraft is full of eels.

kpsqlcent Old Hand Points: 385 More actions · Answer 5

This is a "feature" of maintenance plans. If *any* of the databases in your plan are in SIMPLE mode then *none* of your log backups are cleared down. Personally I think it's a bug but Microsoft don't think so, SQL2000 has always worked that way. We use an external process to remove old log backups.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 6

If you want to use Maintenance plans - create two - one for system database one for user databases. Change your PUBS and Northwind if you have them to Full. I think this is the easiest way for you to get out the full dilemma on system databases and still use the easy to manage maintenance plans.

Also, when you are looking for why your job associated with a maintenance plan exited with a failure code, right click on the maintenance plan (not job) and choose history. You often can find the source of a failure in this history. Sometimes I have seen the real error not even show up here but it's more likely to give you a better idea than job history.

sswords SSCrazy Eights Points: 8207 More actions · Answer 7

I believe what kpsqlcent is saying applies to a situation where you have created one maintenance plan to manage all of your databases. I normally prefer to have a seperate plan for each database for this very reason. If for whatever the cause one job fails, it won't affect completion of the other jobs for your other databases.

I also like to rename the jobs once they've been created so that they all start with the DB name. That way when you look under jobs, the ones associated with each database are grouped together and I find it easier to manage this way.

Jane makes a good point about looking under the plan history as this will tell you at the far right how many files were deleted. I don't know why this detail isn't available under the Jobs section.

Hope this helps.

My hovercraft is full of eels.

Ken Keith-243697 SSC Enthusiast Points: 150 More actions · Answer 8

Thanks for all the input...I actually feel like I'm learning...just too slowly for my liking.

Just to let you know where I'm coming from: I'm a Programmer/Analyst by trade who has been asked to wear the DBA hat for my company. I've done db table design, written programmatic access into databases and written stored procedures(mostly Informix) but never until now been involved at this level of a database.

Last night I had changed all of the DB's that had an error thrown on them from Simple to Full. Seems to have fixed all of the errors except for master, which still gave me the same error and thus my deletion step did not happen.

We're running 3rd party software that uses SQL Server to manage it's data and every project that we do (we do 100's a year) gets a new database created for it.

So I believe that's why the maintenance plan was originally set up to do ALL databases because we always have new ones coming into the system.

Is there a way I can create a maintenance plan for ALL DB's except X, Y and Z databases...because I think that's what I need. It looks like I need a plan that covers ALL databases except master.

It doesn't seem practical for me to specify a maintenance plan for specific DB's when new ones are constantly being added.

Any thoughts? Is this something I really can't do thru the automated maintenance?

If anyone has any ideas I would appreciate it...heck, even a good "SQL Server, Backups, The Girl and The Gold Watch for Dummies" book recommendation would be appreciated.

Thanks

Ken Keith-243697 SSC Enthusiast Points: 150 More actions · Answer 9

Never mind my previous post...I just saw in the maintenance wizard I can specify system DB plan and All other...Doh!!!!

Sorry for the bother...I'm off to play with these new toys!!!

Again...thanks for the help.

sswords SSCrazy Eights Points: 8207 More actions · Answer 10

Ken,

Yes, you can specify these however I don't believe (anyone correct me if I'm wrong) that new DB's will be added once the plan has been created. This is because the SQL code to run the plan is generated at the time when the plan is created. Andy Warren (one of the site owners) wrote an excellent article about looking under the hood of maintenance plans a few years back and that is available here:

http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp

In your case where you are frequently adding databases, it might be more convenient for you to script out the backup job creation. Then you only need to run this script against the newly created databases instead of using the wizard each time. You can find dozens of backup scripts and hopefully one that meets your needs here:

http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=Backup%20/%20Restore&categoryid=8

Hope this helps.

My hovercraft is full of eels.

Ken Keith-243697 SSC Enthusiast Points: 150 More actions · Answer 11

Just took a look at my output directories for DB and Transaction log backups and I seem to have output there for recent db's that were added...some as recent as 2 days ago.

I will however take a look at both the article and the scripts.

Not sure if I'm "ready" to make the jump from "ease of use" maintenance scripts to "more control" manual scripts but will see.

Thanks

kpsqlcent Old Hand Points: 385 More actions · Answer 12

If you use a generic plan (e.g. All or All user databases) then new databases are added in. Really handy and the main reason we use maintenance plans. I'm sure this is the case because we run regular auto-checking scripts looking for databases without backups.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 13

Agree, the main reason for using maintenance plans is the ease of use for things like new databases getting backed up automatically when all user databases are picked and the nice delete/cleanup functions.

I agree with the wish that it would ne nice to have an "all user databses EXCEPT:" option. For servers where you have lots of databases - coming and going all the time but have 1 or 2 you want to handle special.

sswords SSCrazy Eights Points: 8207 More actions · Answer 14

Thanks for the clarifications you all. Just goes to show that you learn something new every day.

My hovercraft is full of eels.