June 26, 2017 at 1:38 pm
Created a maintenance plan to do a full backup of all the databases. Also indicated to create txt log files. The maint plan is attached to a agent job. I can view the history of the agent job (which reports a failure) but can't view the history of the maintenance plan itself. It throws an out of memory error. I've heard this is a known issue but how do I work around it? I've even tried the new SSMS 17.1 but it fails also.
I have jobs/maintenance plans for differential backup and log backups as well. They seem to work fine.
I'd expect log files to be generated when executing the maintenance plan for full backup but none are created. The regular SQL error log has no mention of it either. How can I determine why this maintenance plan is failing or not even running at all? Also how can the history of the maintenance plan be viewed?
Thanks.
June 26, 2017 at 4:59 pm
wdonovan - Monday, June 26, 2017 1:38 PMCreated a maintenance plan to do a full backup of all the databases. Also indicated to create txt log files. The maint plan is attached to a agent job. I can view the history of the agent job (which reports a failure) but can't view the history of the maintenance plan itself. It throws an out of memory error. I've heard this is a known issue but how do I work around it? I've even tried the new SSMS 17.1 but it fails also.
I have jobs/maintenance plans for differential backup and log backups as well. They seem to work fine.
I'd expect log files to be generated when executing the maintenance plan for full backup but none are created. The regular SQL error log has no mention of it either. How can I determine why this maintenance plan is failing or not even running at all? Also how can the history of the maintenance plan be viewed?Thanks.
You need to look at the details for the job history - it would have more than just failure. Expand the history by clicking on the plus sign. You will also want to check the SQL Server Agent log file for errors with the job. Did you setup the job itself to create a log file? Where did you set up the logging to the text file and did you verify that permissions for the folder? If you set it up in the job and no log is created from the job, it's typically a permissions issue.
For Maintenance plan history, Did you try querying msdb.dbo.sysmaintplan_log table? If you right click on a Maintenance plan and select view history, it's basically selecting from that table as well as sysmaintplan_plans and sysmaintplan_subplans. You will want to do a count to check the number of records first. Too many records in that table was one of the reasons for the out of memory error. That can be cleaned up by using sp_maintplan_delete_log if needed.
If you open the Maintenance plan to modify, go to the Backup Database task and click on Edit, you can double check the properties for the plan (server name, backup target, etc) but you can also click the View T-SQL which will allow you to view the code that is being executed. If you have more than one task, make sure you have setup precedence constraints so that the tasks flow from one to another in the appropriate order.
Sue
June 27, 2017 at 6:50 am
Hi Sue,
Thanks for the response.
Expanding the history event in the job history reveals this:
Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:00:00 AM DTExec: Could not set \Package\Weekly BU.Disable value to false. Started: 12:00:00 AM Finished: 12:00:01 AM Elapsed: 0.296 seconds. The package execution failed. The step failed.
Not sure what "Could not set \Package\Weekly BU.Disable values to false. means. Weekly BU is the step name in the job. There's no log setup directly from the job. The logging is configured in the maintenance plan and there are several other plans that log to the same location and they seem to work fine.
Queried the msdb.dbo.sysmaintplan_log table. Only 233 records in it. Nothing that gives any descriptions of anything. The sysmaintplan_plans and sysmaintplan_subplans table have 6 records each. I queried the sysmaintplan_logdetail table also but found no records for the date the maintenance plan was supposed to run. If I run the plan myself by right-clicking>execute, I see records for it in the sysmaintplan_logdetail table.
Seems odd to me that running the maintenance plan myself works fine but if it's called from the job, it doesn't run.
June 27, 2017 at 8:09 am
wdonovan - Tuesday, June 27, 2017 6:50 AMHi Sue,
Thanks for the response.
Expanding the history event in the job history reveals this:
Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:00:00 AM DTExec: Could not set \Package\Weekly BU.Disable value to false. Started: 12:00:00 AM Finished: 12:00:01 AM Elapsed: 0.296 seconds. The package execution failed. The step failed.
Not sure what "Could not set \Package\Weekly BU.Disable values to false. means. Weekly BU is the step name in the job. There's no log setup directly from the job. The logging is configured in the maintenance plan and there are several other plans that log to the same location and they seem to work fine.Queried the msdb.dbo.sysmaintplan_log table. Only 233 records in it.
Nothing that gives any descriptions of anything. The sysmaintplan_plans and sysmaintplan_subplans table have 6 records each. I queried the sysmaintplan_logdetail table also but found no records for the date the maintenance plan was supposed to run. If I run the plan myself by right-clicking>execute, I see records for it in the sysmaintplan_logdetail table.Seems odd to me that running the maintenance plan myself works fine but if it's called from the job, it doesn't run.
It is due to how maintenance plans work and how they are called when you schedule them in a job. The subplans are disabled until you enable them - the job does that. If you run it manually, it doesn't go through that process. So this one maintenance plan has problems enabling the subplan for the weekly backup.
Some weird errors like that get addressed in service packs so make sure you are on the current one - and you need to make some plans to upgrade ASAP.
There is a workaround listed in a blog about using BIDS, create a new SSIS project, import the maintenance plan and set the subplan to enabled (set the disabled property to false). Then when you schedule it in the job, you can remove that SET line from the command. You will see that if you select the job, select properties, go to the subplan step and select Edit. Then check the command by clicking on the command line tab. You will see where it has:
/SET "\Package\Weekly BU.Disable";false
This is the article that explains this:
Running a Maintenance Plan Using dtexec.exe Command Prompt Utility Does Not Perform Any Action
Sue
July 10, 2017 at 2:56 pm
Turns out there was a second job setup calling the same maintenance plan. Once I disabled that second job it started working.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply