Solved

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

Posted on 2014-09-16
10
350 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 48

Expert Comment

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

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 48

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 48

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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

830 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