msdb.dbo.sysjobhistory data gets deleted whenever SQL Server services are restarted

Posted on

Question :

I am facing an issue where my msdb.dbo.sysjobhistory data is getting deleted whenever my SQL Server services are restarted or the instance fails over.

I am using SQL Server 2014 SP2 in a cluster environment, with below SQL Agent History setting:

enter image description here

Can anyone please suggest what the issue is?

Answer :

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!

Leave a Reply

Your email address will not be published. Required fields are marked *