Hi all,

I have a job that uses the utility sqlmaint to create backups for user dbs on one sql server 2005. in case in the corresponding step an error appears, the job is set to go to next step. now - the problem is that after the sqlmaint throws an error, it does not come out immediately - but still executes few more statements - i saw them in profiler (but without any export in step's report file). this seams very strange to me. please help. i have no more ideas (i tried try / catch, returned value from backup execution, more raiserrors...)

10x

uhhhh what's the error?

the initial error was thrown because the old backup file, that had to be deleted after the new backup was created, was locked (in read mode) by another process... therefore sqlmaint ended with error. but i tried another error - i put the db offline... and i got the same strange behaviour (error, but still execution of next statements)
just to be clear - not the error is the problem - because that process must run anyway... so might happen again to lock a file that is needed by sqlmaint. the problem is why does not get out immediately from job step

Did you check if you have a "retry" on the job?

what do you mean by "retry" ?

sp_add_jobstep [ @job_id = ] job_id | [ @job_name = ] 'job_name' [ , [ @step_id = ] step_id ] { , [ @step_name = ] 'step_name' } [ , [ @subsystem = ] 'subsystem' ] [ , [ @command = ] 'command' ] [ , [ @additional_parameters = ] 'parameters' ] [ , [ @cmdexec_success_code = ] code ] [ , [ @on_success_action = ] success_action ] [ , [ @on_success_step_id = ] success_step_id ] [ , [ @on_fail_action = ] fail_action ] [ , [ @on_fail_step_id = ] fail_step_id ] [ , [ @server = ] 'server' ] [ , [ @database_name = ] 'database' ] [ , [ @database_user_name = ] 'user' ] [ , [ @retry_attempts = ] retry_attempts ] [ , [ @retry_interval = ] retry_interval ] [ , [ @os_run_priority = ] run_priority ] [ , [ @output_file_name = ] 'file_name' ] [ , [ @flags = ] flags ] [ , { [ @proxy_id = ] proxy_id | [ @proxy_name = ] 'proxy_name' } ]

no, i didn't had the retry param set. but... after you said about that, i tried that... and still... tries to execute the sqlmaint, this is throwing an error, and still executes next statements before entering in "re-trying" state, without logging in report file.

What is the version/edition of your SQL box?

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

Script your job and post it here.

this is a simple version, just to test what is happening / also - with param @retry_attempts set:

USE [msdb]
GO
/****** Object: Job [my_job] Script Date: 07/26/2010 08:09:47 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[my]]] Script Date: 07/26/2010 08:09:47 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[my]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[my]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'U4S_job',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'my - create backup files',
@category_name=N'[my]',
@owner_login_name=N'my_user', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [s1] Script Date: 07/26/2010 08:09:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N's1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=2,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
print ''Start job Step 1 at ''+convert(varchar(23), getdate(), 121)

EXECUTE master.dbo.xp_sqlmaint N''-D db_try
-Rpt "c:\temp\U4S_DBBackup_rep.txt" -DelTxtRpt 2minutes
-WriteHistory -BkUpMedia
DISK -BkUpDB "c:\temp" -DelBkUps 2minutes -BkExt "BAK"''

if not exists (select * from tempdb..sysobjects where name = ''dei2'' and xtype = ''U'')
create table tempdb..dei2(i int)

print ''before insert''
insert into tempdb..dei2 (i) values (10)
insert into tempdb..dei2 (i) values (20)
insert into tempdb..dei2 (i) values (30)
insert into tempdb..dei2 (i) values (40)
insert into tempdb..dei2 (i) values (50)
print ''after insert''
update tempdb..dei2 set i = i + 1 where i < 50
delete tempdb..dei2 where i >= 30

print ''Stop job Step 1 at ''+convert(varchar(23), getdate(), 121)
',
@database_name=N'master',
@output_file_name=N'c:\temp\s_report.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule_DBBackup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100301,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

OK, I see that everything following "EXECUTE master.dbo.xp_sqlmaint " is for testing purposes. Is this what you referred to when saying that the step does not fail? If that's the case, you need to check for return code:
I haven't run it, but should work.

yes, indeed, i tried to catch the returned value - it comes right, and - i tried to raise error => same behavior, so... i forced a goto a label at the end of the code. but - still - i don't like this solution - does not seam ok, normal, elegant...
10x for your replays - if you have any other ideas, i hope you will let me know
all the best

I'm a little confused by what you're trying to do. On the one hand you're attempting to perform a full backup, but on the other you're using deprecated command to do it. You like re-writing your own code? SQLMAINT.EXE existed since 6.5, and has proven to be the most reliable method of performing these types of tasks. Change your job subsystem to CMDEXEC, and use SQLMAINT in 1 step. Move everything else out of that job. Set step dependency however you like (to continue with the next step after error(-s), to jump to a different step, or to terminate the job alltogether). Try to make it simpler. All complex things are made out of very simple components.

you are giving a good advice, but... i tell you the origin of this job
this job is included in a maint. plan for a server with more than 200 production dbs, and the maint. plan includes jobs for index defrag, backup, log cleaning, update statistics (for db's which do not have it set automatically), integrity check...
the main problem is the limited time. therefore, (almost) all jobs are synchronized - even if some of them are starting in the same tine, there must be an order in accessing a db. and, of course, the order is kept using some code. more - even if sqlmaint throws error, all backups must be created during the required time (except problematic dbs - and their number is unknown - usually zero, but... you never know) - so, this job shouldn't just go out when it encounters some difficulties.
on the other side... when this maint plan was written, the sqlmaint was the best choice, as it knows to delete the old backup immediately after the new one is created (also - there is a limited space)
i know sqlmaint is already deprecated - but we didn't find, until now, a good replacement for it. i suppose we will rewrite the jobs using the clasic BACKUP command, but... still... sqlmaint is nicer and... i expect sqlmaint to behave well