Avatar of marrowyung
marrowyung
 asked on

MS SQL 2019 SQL job, after finished, never update the job history

hi,

one of our SQL server 2019 run into a funny CHARACTERS, The SQL job, after finished, never update the job history last run time but only next run time.

history always old record.

any idea ? patch can help? 
Microsoft SQL Server

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
lcohan

I would definitely look into patching the SQL Server Engine, SQL Agent and SSMS that you're using to view the history. As side note and before patching could you try the command below see if the history is actually there after the job finished?
USE msdb ; 
GO 

EXEC dbo.sp_help_jobhistory  @job_name = N'YourJobNameHere' ; 
GO 

Open in new window


marrowyung

ASKER
If I run the job I see this error in error 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.4083&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
Server Name: 10.0.1.101
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)

but If I run the step individually, the job is good.

what is the error means ?
ASKER CERTIFIED SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
Your question was "MS SQL 2019 SQL job, after finished, never update the job history" and I suggested to apply the patches and have you done that?

only 1 x SQL server out of 4 of the same patch level has this problem, so it is not about the SQL server patches, right?
What you asked above seems to be totally unrelated to your original question and not clear if this error was always there OR showing now in the Job History after you patched the server.

then error is found if I run the job manually, so can be a good hints.

and if I run the scrip inside the job individually, it is ok.


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
marrowyung

ASKER
marrowyung

ASKER
tks.

we finished applying latest patch,it fixed that. SQL serve patch/cU quiet stable actually !