Solved

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

Posted on 2014-09-16
10
452 Views
Last Modified: 2014-09-24
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
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40327409
Did you check the date of your server?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40327423
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
 

Author Comment

by:al4629740
ID: 40329454
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40329777
So, something happened after 7/13/2014. Did you check the server date?
0
 

Author Comment

by:al4629740
ID: 40330151
Server date is fine
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40330157
Try to run manually some of those jobs. For example 'syspolicy_purge_history' and then check again for the Next Run Date.
0
 

Author Comment

by:al4629740
ID: 40330220
Where do I find that job?  Example?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40330228
In Job Activity Monitor. You can find there all jobs.
0
 

Author Comment

by:al4629740
ID: 40340318
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
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40340944
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question