SQL Server Performance Forum – Threads Archive
Job History automatically getting deleted
Job history is automatically, all of sudden started getting deleted on its own. I have executed trace to see if any person is executing sp_purge_jobhistory, but couldn’t find anything. There is lot of sql server activity so can’t trace every activity. Job history is getting deleted after couple of minutes on its own, and this is for all jobs. Any ideas????Check the SQL server agent properties…
Right clikc sql agent/properties/History
Make sure you have configured correctly history log depends on your jobs…
By default MAXIMUM JOB HISTORY LOG SIZE is 1000 and MAXIMUM JOB HISTORY ROWS PER JOB is 100….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
If you need more than the default value then make sure to increase the MSDB to atleast 500mb as the job schedule seems to pretty high. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
MSDB is set to autogrow to indefinate values and I have checked the SQL SERVER AGENT PROPERTIES. Its currently set on Maximum Job History Log size = 10000
Maximum Job History rows per job = 100
Automatically remove job history is not checked in, so it should not get deleted automatically.
This values have not changed and we use to have job history for atleast 20 days, however now all of suddent we don’t have more than 40 rows in sysjobhistory and even currently executed job with one line history is getting deleted on its own.
Check if any one scheduled Sp_purge_jobhistory procedure to run every minute or so.
And the run sql server profiler and filer with "Sp_purge_jobhistory" to see who is running to clean history… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I have checked the jobs and even run a trace for 1 hour to see who is running this stored procedure, but nothing came obvious.
Is there any other command to delete job history??
I believe only two ways other than chanignt the row limit… EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=’2007-03-11T16:34:55′ delete from msdb..sysjobhistory
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
DO you have any other job or process to refresh the msdb job history or use any third party tool for job scheduling>? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>