maintenance plan not running even though it is scheduled - SQL 2012

I have a maintenance plan that stopped a couple months ago and just found out today.  When I troubleshoot, I notice that it is scehduled to run every day but the Job Activity Monitor shows the next job is to run next year!

I attached the following screens and hope someone can figure out what I did wrong.
sample5.docx
al4629740Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
syspolicy_purge_history it's a system job that will clean the history of the policies older than what's predefined in the HistoryRetentionInDays (check the Properties of Policy Management).

To recreate the job run the following script:
DECLARE @jobId uniqueidentifier
 -- Obtain the current job identifier that is associated with the PurgeHistory
 SELECT @jobId = CAST(current_value AS uniqueidentifier)
 FROM msdb.dbo.syspolicy_configuration_internal
 WHERE name = N'PurgeHistoryJobGuid'
 -- Delete the job identifier association in the syspolicy configuration
 DELETE FROM msdb.dbo.syspolicy_configuration_internal
 WHERE name = N'PurgeHistoryJobGuid'
 -- Delete the offending job
 EXEC msdb.dbo.sp_delete_job @job_id = @jobId
 -- Re-create the job and its association in the syspolicy configuration table
 EXEC msdb.dbo.sp_syspolicy_create_purge_job

Open in new window


You can find this and more information here.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you check the date of your server?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And what this query returns?
USE msdb
GO

SELECT j.name, a.stop_execution_date, a.next_scheduled_run_date
FROM dbo.sysjobs j
	INNER JOIN dbo.sysjobactivity a
		ON j.job_id = a.job_id
WHERE a.stop_execution_date IS NOT NULL
	AND a.next_scheduled_run_date IS NOT NULL
	AND j.enabled=1
ORDER BY j.name

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
al4629740Author Commented:
Query returns

name	stop_execution_date	next_scheduled_run_date
Database Backup.Subplan_1	8/9/2013	8/10/2013
Database Backup.Subplan_1	7/13/2014	7/14/2015
Remove Old Backups.Subplan_1	8/9/2013	8/10/2013
Remove Old Backups.Subplan_1	7/13/2014	7/14/2015
Remove Old T-Log Backups.Subplan_1	7/13/2014	7/19/2015
syspolicy_purge_history	7/29/2013	7/30/2013
syspolicy_purge_history	8/9/2013	8/10/2013
syspolicy_purge_history	7/13/2014	7/14/2015
TLog Backups.Subplan_1	7/13/2014	7/14/2015

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, something happened after 7/13/2014. Did you check the server date?
0
 
al4629740Author Commented:
Server date is fine
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to run manually some of those jobs. For example 'syspolicy_purge_history' and then check again for the Next Run Date.
0
 
al4629740Author Commented:
Where do I find that job?  Example?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In Job Activity Monitor. You can find there all jobs.
0
 
al4629740Author Commented:
Just a follow up, sorry been busy.

I deleted all the jobs and reestablished them.  There is one job still left that is not scheduled to run until next year.  It is the syspolicy_purge_history.  (See attachment)  What do I do to fix this?  What is the purpose of this job?
syspolicypurgehistory.docx
0
All Courses

From novice to tech pro — start learning today.