SQL Agent Job is stuck

Question

Post reply

SQL Agent Job is stuck

John Esraelo-498130

SSCertifiable

Points: 5914
More actions
March 31, 2021 at 5:50 pm

#3864109
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

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

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

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

did you hit the refresh button in SSMS ?
can you see the job active in SQLAgent
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

Sergiy SSC Guru Points: 110206 More actions · Answer 2

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

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Once I fixed all the < and > 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)

Sergiy SSC Guru Points: 110206 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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)

ScottPletcher SSC Guru Points: 100947 More actions · Answer 8

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) A socialist is someone who will give you the shirt off *someone else's* back.

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 9

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

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 10

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 11

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