April 27, 2010 at 12:12 am
Hi,
I found the following error message from the one of the sql jobs which has been scheduled for backup at every day.
Executed as user: XXXXXX\YYYYY. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
when i run manually its running fine without any error message.
can anybody tell me what is the reason for this error message?
April 27, 2010 at 12:19 am
Hi,
Please provide more information. Is it Maintenance Plan? If so, Right click on that and view history. Here you can find more detail about M.P. than history provided by SQL Job.
Sani
April 27, 2010 at 12:37 am
sani (4/27/2010)
Hi,Please provide more information. Is it Maintenance Plan? If so, Right click on that and view history. Here you can find more detail about M.P. than history provided by SQL Job.
Sani
Thanks for your reply ...
Yes this job has been created by maintenance plan only.but i could not find any error message on the history of the maintannace plan.
There were two jobs running at the same time on is system database jobs and another one is User database. I am getting error message from user database job only.
April 27, 2010 at 2:01 am
Error Message from Maintanance Plan Text File...
[5] Database DatabaseFive: Delete Old Backup Files...
Unable to delete file G:\MSSQL\BACKUPS\DatabaseFive\DatabaseFive_db_201004251900.BAK. 0 file(s) deleted.
End of maintenance plan 'Maintenance Plan' on 4/27/2010 7:01:24 AM SQLMAINT.EXE Process Exit Code: 1 (Failed)
April 27, 2010 at 6:06 am
Does that file even exist? If so, is it perhaps locked by another process?
April 27, 2010 at 6:12 am
Brandie Tarvin (4/27/2010)
Does that file even exist? If so, is it perhaps locked by another process?
No that files is not exist and I could not find when & how it had got deleted from that path.I cannot say it may be permission issue since it was running with the same user for the last six months.
Thanks in Advance.
April 27, 2010 at 6:17 am
Well that seems to be the problem right there. The job is failing by trying to delete a file that doesn't exist.
Perhaps you should check the job, see if this is a coded step (which I'm guessing it is) or a specific maint. plan task. If the later, open up the task and see what's going on.
EDIT: Get a list of the job tables from Books Online (BOL), find the table that keeps the backup history (don't remember it off the top of my head) and see if that file is still listed as an official backup. That might also be the problem. You may have to create a Faux File for the job to delete.
April 27, 2010 at 6:26 am
Brandie Tarvin (4/27/2010)
Well that seems to be the problem right there. The job is failing by trying to delete a file that doesn't exist.Perhaps you should check the job, see if this is a coded step (which I'm guessing it is) or a specific maint. plan task. If the later, open up the task and see what's going on.
EDIT: Get a list of the job tables from Books Online (BOL), find the table that keeps the backup history (don't remember it off the top of my head) and see if that file is still listed as an official backup. That might also be the problem. You may have to create a Faux File for the job to delete.
Thank you for your reply ...
Actually This job failed and got mail alert then i verified that job and start the job manully.that time it was running sucessfuly without any error message.
April 27, 2010 at 8:00 am
I'm glad it's working now. Sounds like the filename was kept in a table and re-running might have cleared the cache.
April 27, 2010 at 11:02 am
Brandie Tarvin (4/27/2010)
I'm glad it's working now. Sounds like the filename was kept in a table and re-running might have cleared the cache.
Hi ,
I could not get you.can you elaborate about it.i am trying to find out the root cause of that error message.
Thanks in Advance.
April 27, 2010 at 11:56 am
Brandie is referring to (I think) when you take a backup of a database, that information (database name, timestamps, file name and path) are kept in system tables. When you add a cleanup task to remove backups files older than X, it checks those tables for the file(s) that need to be deleted.
These 2 links give you the System table list for backups and maintenance plans:
Actually This job failed and got mail alert then i verified that job and start the job manually.that time it was running successfuly without any error message
So you do not have any errors now or are you still experiencing issues?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 28, 2010 at 1:15 am
MeltonDBA (4/27/2010)
Brandie is referring to (I think) when you take a backup of a database, that information (database name, timestamps, file name and path) are kept in system tables. When you add a cleanup task to remove backups files older than X, it checks those tables for the file(s) that need to be deleted.These 2 links give you the System table list for backups and maintenance plans:
Actually This job failed and got mail alert then i verified that job and start the job manually.that time it was running successfuly without any error message
So you do not have any errors now or are you still experiencing issues?
Thanks for your reply ...
Now i dont have any issues on the job. its working fine. am trying to find out the root cause for that job failure. I hope it wont refer the system tables while deleting the old backus,it will search in the perticular drive and take the old backup files based on the retention period mentioned on the maintanance plan and then do the deletion. Please correct me if i am wrong on this.
April 28, 2010 at 4:14 am
The maintenance plans will always look in the system tables for backup file names when cleaning up old data. The only way to delete old backups without affecting the system tables is to use a T-SQL task (xp_cmdshell) or a Windows Task.
And I only recommend that approach if you've got copies of the backup files elsewhere. Otherwise, you'll seriously screw up your backup file history.
April 28, 2010 at 7:51 am
Actually I mis-informed you on something.
The Maintenance Cleanup Task does not touch any system table at all. It will check the directory you specifiy in the "Folder Field" of the dialog box. It will also not touch the system tables and remove any history information.
To cleanup the history information on backups and restores you should schedule a History Cleanup Task and that will purge information from the backup system tables.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 15, 2010 at 4:32 am
Hi,
Again i have received the same problem on the sql server for another job. i have verified all the jobs there was no overlapping while running the job and that folder also has full control access for user everyone.so there is no problem with permission as well.
"[3] Database Six: Delete Old Backup Files...
Unable to delete file X:\Backups\MSSQL\Six\Six_db_201005132000.BAK. 0 file(s) deleted."
pls update your feedback to find out the reason of this issue.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply