Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-09-16
10
Medium Priority
?
598 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
  • 6
  • 4
10 Comments
 
LVL 52

Expert Comment

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

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 52

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 52

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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

824 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