Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2010
    Posts
    11

    Unanswered: sql server job for backup - generates error

    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

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    uhhhh what's the error?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2010
    Posts
    11
    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

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Did you check if you have a "retry" on the job?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2010
    Posts
    11
    what do you mean by "retry" ?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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' } ]
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2010
    Posts
    11
    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.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What is the version/edition of your SQL box?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2010
    Posts
    11
    Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Script your job and post it here.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jul 2010
    Posts
    11
    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:

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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:
    Code:
    declare @retVal int, @errMsg varchar(max)
    EXECUTE @retVal = master.dbo.xp_sqlmaint ...
    if @retVal != 0 begin
       select @errMsg = 'Error: cast(error_number() as varchar(10)) + ', ' + error_message()
       raiserror ('Failed to execute sqlmaint (%s)!', 15, 1, @errMsg)
       return;
    end
    I haven't run it, but should work.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jul 2010
    Posts
    11
    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

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jul 2010
    Posts
    11
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •