Posts
My SQL Server job is failing throwing errors as shown below
[SQLSTATE 01000] (Message 3211) Write on
"\\backup-location\folder\database_name.BAK"
failed: 2(The system cannot find the file specified.) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The sql jobs are failing due to network dis-connectivity to the network location where my backups will be written to.
So I made use to retry_attempts and retry_interval parameter options available in job properties. as show below pic.
But I have 102 steps, I don't want to waste my time in updating all the job steps, instead I want to update all steps at once. Here is the query that I used to update all steps of a particular job that I wanted to update retry_interval and retry_attempts of a job.
TO SET RETRY ATTEMPTS to the job
Update sysjobsteps
Set retry_attempts=3 (--set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name='JOB NAME'
TO SET RETRY INTERVALS to the job
Update sysjobsteps
Set retry_interval=1 (--set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name='JOB NAME'
Note: Run at your own risk. It worked for me. This may be not the ideal solution since it is more related to the Network packets being dropped, and in-stable Network. I spent several months on resolution for network issues. But I used this, hope this helps others.
Thanks for clarifying such details! Faced above error for two month with 110 gb db when backup to network share the throws errors with status = 1450 (1450 insufficient system resources), then sqlstate 4200 error 3202 and error 3013. Agent job for backup db to network location some times executed normal, sometimes throw these errors instantly. Read alot of articles related to os regkeys pagedpoolsize, poolusagemaximum, irpstacksize it doesn't seem to workaround here and that error 1450 is total misleading to resolve the problem. I thought about time crossing of the scheduled jobs at evenings and nights at first. Changed times of this error job with no luck. And then thought about retry attempts and such errors goes away when i set retry attempts and intervals. Then again set them off and restart the server errors goes back. Main detail that above errors 3202 and 3013 coul appear at different day intervals. I tried evenings, mornings,nights. So thanks again!
ReplyDelete