0

I am facing a issue where my msdb.dbo.sysjobhistory data is getting deleted when ever my SQL Server services are restarted or fail over.

I am using SQL Server 2014 SP2 with cluster environment, with below settings:
MaximumHistoryRows -1
MaximumJobHistoryRows 0

Can anyone please suggest what the issue is?

  • What's History setting in SQL Server Agent? – Rajesh Ranjan Dec 24 '18 at 7:43
  • @AdityaSawant, What is SQL Server version(x,y,z)? – Md Haidar Ali Khan Dec 24 '18 at 9:48
  • How much job history you want to retain? Is it for a certain time for all jobs or each job has specific retention time? – SqlWorldWide Dec 24 '18 at 13:38
  • @Md Haidar Ali Khan, My SQL Server version is SQL Server 2014 (SP2-CU11) – Aditya Sawant Jan 16 at 5:22
0

My response is based on possible reasons of problem. Make sure you have configured History setting properly and the job to purge history scheduled properly. To validate these settings:

Right click on SQL Server Agent and got to Properties.

enter image description here

Go to History tab.

enter image description here

Make sure setting is configured properly.

enter image description here

The job named syspolicy_purge_history used to execute above settings.

So right click on the job in Jobs folder in SQL Server Agent and check Schedule of the Job.

enter image description here

It should be scheduled according to your requirement.

Thanks!

  • I have seen my sql Agent history settings and it is not set to any limited size also my syspolicy_purge_history job is set to recurring schedule which does not deletes all the history of all jobs but when my sql server is restarted all my job history are deleted . – Aditya Sawant Jan 3 at 17:12
  • Please validate other jobs executes on start of SQL Server Agent. – Rajesh Ranjan Jan 24 at 7:40
  • I have validate all other jobs on my SQL Server No jobs execute on start of SQL Server Agent. – Aditya Sawant Feb 26 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.