SQL Agent Job is stuck

  • Hello,

    Here is the situation.

    • SQL 2019 enterprise on datacenter server
    • agent is running
    • test job has 1 line;   select getdate() or running a SPROC with the same line
    • the job runs forever or errors without history

    any ideas?

     

    Cheers,
    John Esraelo

    • did you hit the refresh button in SSMS ?
    • can you see the job active in SQLAgent

      2021-04-01 08_42_53-Object Explorer Details - Microsoft SQL Server Management Studio

    • can you post the jobs definition ?

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Check what's going on with msdb. Where are the files located, size of it, any spikes, available space, locking, etc.

    _____________
    Code for TallyGenerator

  • John Esraelo-498130 wrote:

    Hello,

    Here is the situation.

    • SQL 2019 enterprise on datacenter server
    • agent is running
    • test job has 1 line;   select getdate() or running a SPROC with the same line
    • the job runs forever or errors without history

    any ideas?

    Perhaps this...

    https://www.sql.kiwi/2021/03/spools-batch-mode-hp.html

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Here is the definition.

    It is a simple 1 liner. The perfmon show that there are several retries before it crashes.

    The services are running.

    USE [msdb]
    GO

    /****** Object: Job [test] Script Date: 4/5/2021 12:50:01 PM ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 4/5/2021 12:50:02 PM ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test',
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=0,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N'No description available.',
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object: Step [popcorn] Script Date: 4/5/2021 12:50:02 PM ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'popcorn',
    @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=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'

    select current_timestamp


    ',
    @database_name=N'_SandBox',
    @flags=12
    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_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:
    GO


    Cheers,
    John Esraelo

  • Once I fixed all the &lt; and &gt; entitizations and changed the database name to one of the one I have, it ran first time with no issues.  I wonder if you have a database name that starts with an underscore might be a part of the issue?  Haven't tried it, though.  I'll leave that up to you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I created [_sandbox] database for this purpose, deployed the script and scheduled it to run every minute.

    It's been 15 or 20 executions since then - no issues.

    I'm telling you - check the well being of you msdb database.

    _____________
    Code for TallyGenerator

  • Sergiy wrote:

    I created [_sandbox] database for this purpose, deployed the script and scheduled it to run every minute.

    It's been 15 or 20 executions since then - no issues.

    I'm telling you - check the well being of you msdb database.

    Totally agree.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • It seems there must be some error occurring when the script it exec'd.  Add some msgs to (1) verify if this is true and (2) if so, show the return code (you can add additional debugging info to the problem area if you find one).

    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 4/5/2021 12:50:02 PM ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    BEGIN
    PRINT 'Error adding job category, return code = ' + CAST(@ReturnCode AS varchar(10)) + '.'
    GOTO QuitWithRollback
    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test',
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=0,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N'No description available.',
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    BEGIN
    PRINT 'Error adding job, return code = ' + CAST(@ReturnCode AS varchar(10)) + '.'
    GOTO QuitWithRollback
    END
    /****** Object: Step [popcorn] Script Date: 4/5/2021 12:50:02 PM ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'popcorn',
    @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=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'

    select current_timestamp


    ',
    @database_name=N'_SandBox',
    @flags=12
    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    BEGIN
    PRINT 'Error adding jobstep, return code = ' + CAST(@ReturnCode AS varchar(10)) + '.'
    GOTO QuitWithRollback
    END
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    BEGIN
    PRINT 'Error updating job, return code = ' + CAST(@ReturnCode AS varchar(10)) + '.'
    GOTO QuitWithRollback
    END
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    BEGIN
    PRINT 'Error adding job server, return code = ' + CAST(@ReturnCode AS varchar(10)) + '.'
    GOTO QuitWithRollback
    END
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • The _Sandbox exists in other servers without any issues. That's correct.

     

    Cheers,
    John Esraelo

  • Yes, agree. I have used the advanced option to write out the error / message / etc. but it does not get there.. and there is no history, here is the error message in detail.

    ===================================

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
    at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
    at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
    at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query, Object con)
    at Microsoft.SqlServer.Management.Smo.PostProcessJobActivity.GetColumnData(String name, Object data, DataProvider dp)
    at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
    at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable()
    at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
    at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
    at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
    at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
    at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
    at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
    at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
    at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
    at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
    at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
    at Microsoft.SqlServer.Management.Smo.Agent.Job.get_LastRunDate()
    at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.WaitForJobToFinishAction.DoAction(ProgressItemCollection actions, Int32 index)
    at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

    ===================================

    A severe error occurred on the current command. The results, if any, should be discarded. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4102&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

    ------------------------------
    Server Name: SQL-DEV
    Error Number: 0
    Severity: 11
    State: 0


    ------------------------------
    Program Location:

    at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)

    Cheers,
    John Esraelo

  • Nice piece to read.

    Well, gurus, I have decided to trash that instance and have created another one.  It was a new dev box that did not work well.

    I will chalk this one as unsolved mystery.

    thx all.

     

    Cheers,
    John Esraelo

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply