2

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?

6
  • What's History setting in SQL Server Agent? Dec 24, 2018 at 7:43
  • @AdityaSawant, What is SQL Server version(x,y,z)? Dec 24, 2018 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? Dec 24, 2018 at 13:38
  • @Md Haidar Ali Khan, My SQL Server version is SQL Server 2014 (SP2-CU11) Jan 16, 2019 at 5:22
  • 1
    @Johnakahot2use I have updated by Post with SQL Agent History scree shot Dec 10, 2019 at 22:10

1 Answer 1

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!

4
  • 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 . Jan 3, 2019 at 17:12
  • Please validate other jobs executes on start of SQL Server Agent. Jan 24, 2019 at 7:40
  • I have validate all other jobs on my SQL Server No jobs execute on start of SQL Server Agent. Feb 26, 2019 at 6:36
  • Possibly someone has added a stored procedure and marked it to run on startup you can identify these with this query, check in the master database SELECT [name] FROM sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1 Jan 15, 2021 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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