Trans Log Backup in Maintenance Plan is NOT deleting old log files as requested

Question

Trans Log Backup in Maintenance Plan is NOT deleting old log files as requested

Elisabeth Turner

SSC Enthusiast

Points: 197
More actions
June 11, 2004 at 5:02 am

#108362

I have created a transaction log back up job in a database maintenance plan, and requested that the trans logs are backed up to disk, with any files older than 2 days to be removed.
The Job History within SQL Server Agent indicates that the trans log back up job has failed;
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
The backups HAVE in fact been created, but the failure appears to be in deleting the old files.
Does anybody know what has caused this, or how to fix it?

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

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

jordanac Hall of Fame Points: 3653 More actions · Answer 1

HI,

I have had this problem before and my findings were that it occurs when your database maintenance plan is trying to create a transactionlog backup of a database which is Off-line or when a database is using the Simple Recovery model.

EDIT. OOOPS. You said that the backup did occur so this error seems to have multiple "USES"

Andy.

jordanac Hall of Fame Points: 3653 More actions · Answer 2

Have you tried to set the Reporting options in the Database Maintenance Plan. Here you will get detailed error messages.

Andy.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Check the share and directory level permissions for the SQL Server Agent account putting the files out on the server. The rights might be restrictive for the account.

Shamless self promotion - read my blog http://sirsql.net

Elisabeth Turner SSC Enthusiast Points: 197 More actions · Answer 4

Hmmm, the reporting options are set, and they basically tell me that the backups work, but there is no mention of deleting the old log files. It just simply ends with the message.

End of maintenance plan 'Sun Database Backup - OMNI' on 11/06/2004 15:20:25

SQLMAINT.EXE Process Exit Code: 1 (Failed)

I've tried running the xp_sqlmaint proc manually in query analyser and encounter the same problem.

I don't think it's a problem with share and directory level permissions either, as the database backup, which is part of the same maintenance plan, appears to be purging the old files with no problems!

Jeff Hachmann SSC-Addicted Points: 438 More actions · Answer 5

Like Jordanac said above, it will fail on all databases with the SIMPLE recovery model, it will also fail if there has been an unlogged event in any database, ie Bulk Loads, Table Truncations.

If you have selected "All databases" use the "Backup These Databases" option, for t-logs do not choose Master, msde or tempdb. If all else fails go down the list of databases in the log and check against the databases that should be getting backed up and make sure none on the list are "SIMPLE".

Clear as mud?

THANG HOANG Say Hey Kid Points: 662 More actions · Answer 6

Deleting old backup file is one of the last step that gets executed.

It gets executed only after all of the backup steps are completed sucessfully.

Any prior error would prevent this step from executing, therefore, no old files were deleted.