March 20, 2017 at 2:42 pm
I have noticed on a few of my production instances that there is 'missing' data from the msdb database.
Specifically backup and restore history.
The agent is set to purge history greater than 100,000 rows and we don't have anything in place to remove it via the sp_delete_backup_history and sp_purge_job_history.
An example is our restorehistory table only has 2 rows in it. We have been migrating databases for the last 4 months and all of them have been restored from SQL 2005 into SQL 2012 (via restore jobs) so I would assume there would be restore history in that table.
The other backup history tables have roughly 15,000 rows in it but nothing older than a month ago. We have been running backups for over a year on other databases on this instance.
Is there a way I can determine if something is set up that could be purging the history that I'm maybe not aware of?
There are no Maintenance plans set up on the server and from what I can see no scheduled job that could be doing it.
TIA.
March 22, 2017 at 7:45 am
Agent limits the history in 2 measures - overall and per job.
The number 100k you've mentioned - is it overall number?
If yes - what is the limit "per job"?
The trick here is - if you have several (say, 10) jobs which are executed comparatively often, and your limit "per job" is rather big (say, 10k) then those 10 jobs will fill up all allowed 100k records of the "overall" history, and those occasional history records for the backup job will be removed as "too old".
Reduce the limit "per job" to the number which would allow rarely executed jobs to keep their history records for a reasonable time.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply