July 31, 2003 at 10:39 am
I have a single maintenance plan that backs up all databases nightly and backs up transaction logs every 4 hours. The database backup files are deleted after 5 days, but the log backup files never do (the setting is 2 days).
All database are set for full recovery, and all the transaction log backup files are created on schedule, but the agent history for the log backup job always shows a Failed status with the message "Executed as user: xxx\xxx. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
The backup command (generated by the maintenance plan wizard) is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx -To "IT Alerts" -Rpt "D:\backups\Maintenance Plan on DB16.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\backuplogs" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
I created a separate custom job just to delete the old log backups, but it bothers me whenever I check the SQL Server Agent log and see the nasty red X.
July 31, 2003 at 11:21 am
You should be logging the results somewhere. Check the maint plan to see where teh logs are written and then check the text file for the plan. You will probably find an error there.
Steve Jones
July 31, 2003 at 4:30 pm
There are no errors in the reports.
This is a generic maintenance plan to backup all databases, so it also applies to system dbs. The transaction log backup statements for system databases includes messages like:
Backup can not be performed on database 'master. This subtask is ignored.
The report ends with:
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'xxx' on 7/31/2003 5:01:05 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Other than the "Backup can not be performed" messages, there are only 30 repetitions of "<db>: Transaction Log Backup...", "Destination:", "Execution Time:", "<db>: Verifying Backup...", "Execution Time:"
Just in case the system databases are the problem, I moved the log backups to a separate maintenance plan that only includes user databases.
July 31, 2003 at 5:12 pm
July 31, 2003 at 6:18 pm
That seems to have done the trick. The report now has a batch of "Delete old backup files..." lines after the backup & verify section.
It will be a shame to have to kill the special job I created to delete the old tlog backups, because I had it string the output of xp_fixeddrives into one line and return that as the status. I then use the job history to get a record of the free space ever few hours.
But at least the red X is gone.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply