Link to home
Start Free TrialLog in
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? 
Avatar of lcohan
lcohan
Flag of Canada image

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


Avatar of marrowyung
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
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.


tks.

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