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
- 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) -
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:
GOCheers,
John Esraelo -
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) -
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) -
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:
GOSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply