October 5, 2009 at 9:38 am
Hi Folks
using SSMS, I have user full backups scheduled to run everynight at 12:00 am
as of 9-30-09 they stopped working for some reason
they have been working since 7-9-2009 when i set the jobs up
there are no errors anywhere that i can find
there are no entries in the Application events
there are no entries for them under "View History"
my diff backups and trans backups are still working
does anyone know where i can look to see what has happened to my full backup jobs ?
Thanks
jim
October 5, 2009 at 10:00 am
Is the job enabled AND the schedule enabled ?
October 5, 2009 at 10:13 am
Hi homebrew01
wish it were that simple
Recurring and enabled every day of every week at 11:00pm
Thank
Jim
October 5, 2009 at 10:35 am
HI folks
found some more info, but not sure what it means:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,
Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
10/04/2009 23:00:00,MP-FullBackup-UserDB.Subplan_1,Error,0,NSAB-SS80-SQL-N,
MP-FullBackup-UserDB.Subplan_1,(Job outcome),,The job failed.
The Job was invoked by Schedule 7 (MP-FullBackup-UserDB.Subplan_1).
The last step to run was step 1 (Subplan_1).,00:00:03,0,0,,,,0
10/04/2009 23:00:01,MP-FullBackup-UserDB.Subplan_1,Error,1,NSAB-SS80-SQL-N,MP-FullBackup-UserDB.Subplan_1,Subplan_1,,Executed as user:
LOSANGELES-2K\llspsqlservice.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:00:01 PM Could not load package "Maintenance Plans\MP-FullBackup-UserDB"
because of error 0xC001000A.
Description: The specified package could not be loaded from the SQL Server database.
Source: Started: 11:00:01 PM Finished: 11:00:02 PM Elapsed: 0.703 seconds.
The package could not be loaded. The step failed.,00:00:01,0,0,,,,0
Thanks
Jim
October 5, 2009 at 11:30 am
While you are figuring it out, be sure to create a new job to do the backups.
October 5, 2009 at 11:51 am
Not sure of the issue, but it looks like the package might have issues. If this is a maintenance plan, I'd recreate it. Simple enough to do. Can you open the plan?
Or restore from an msdb backup to a new database and copy it out.
October 5, 2009 at 11:59 am
Or even re-save it as-is and see if that fixes it.
October 5, 2009 at 12:37 pm
Hi Guys
thanks for the advice
I am able to modify the original maint plan and save it
I am going to try and execute it manually from SSMS and see if it now works
if not, i really hate to re-create it as I put a lot of effort into it with T-SQL statements and notifications
How do I copy a maint plan out of an existing database or a copy of the database ?
Thanks
Jim
October 6, 2009 at 8:59 am
FYI
I finally figured out the problem when i ran the Maint Plan manually. Gave me a better message:
TITLE: Execute Maintenance Plan
------------------------------
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
SQLServerAgent Error: Request to run job MP-UserDB-Backup-Full.Subplan_1
(from User NSAB-SS80-SQL-N\lladmin)
refused because the job is already running from a request by Schedule 8
(MP-UserDB-Backup-Full.Subplan_1).
(Microsoft SQL Server, Error: 22022)
why it was still runing from 9-30, i am not sure, but once i cancelled it, i was able to run the Maint Plan
Thanks for all your input
Jim
October 13, 2009 at 2:58 pm
Hi Folks
I thought I had resolved this 0xC001000A issue but I am still getting SQL Agent Error
even though the Plan finishes and does my backups, so i do not understand what is going on
if someone has had this issue, i would appreciate some assistance.
The history on the plan show successful
The report shows success
The SQl Agent show Fail
+++++++++++++++++++++++++
SQL Agent Log:
The job failed.
The job was invoked by Schedule 7 (MP-DullBackup-UserDB.Subplan_1). The last step to run was step 1 (Subplan-1)
Could not load package <package_name> because of error 0xC001000A
Desctiption: The specified package could not be loaded from the SQL Server database
Package History:
all checks are green
Maint PLan Report
Everything is successful
Plan History:
Executed as user: LOSANGELES-2K\llspsqlservice. ... Integrity
Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2009-10-12 23:13:54.75
Source: Check Database Integrity Executing query "USE [QA_WSS_Search_NSAB-SS79-SPC-N] ".: 50% complete End Progress Progress: 2009-10-12 23:13:57.86
Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2009-10-12 23:13:57.89
Source: Check Database Integrity Executing query "USE [QA_WSS_Search_NSAB-SS82-SPF-N] ".: 50% complete End Progress Progress: 2009-10-12 23:14:00.47
Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2009-10-12 23:14:00.50
Source: Check Database Integrity Executing query "USE [ReportServer] ".: 50% complete End Progress Progress: 2009-10-12 23:14:02.09
S... The package executed successf... The step succeeded.
+++++++++++++++++++++++++
Thanks
Jim
October 13, 2009 at 3:28 pm
JC-3113 (10/5/2009)
How do I copy a maint plan out of an existing database or a copy of the database ?
You open the maintenance plan (modify) and then open a new maintenance at the same time. You should be able to copy objects between the current modified one and the new one. Be careful to check your paths and settings, sometimes it will reset some things (checked items, file paths, etc).
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 13, 2009 at 3:38 pm
That seems to work but is there anyway to copy the schedule as well
Thanks
Jim
October 13, 2009 at 3:47 pm
JC-3113 (10/13/2009)
That seems to work but is there anyway to copy the schedule as wellThanks
Jim
Not that I am aware of, you would just have to do that manually.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 13, 2009 at 4:07 pm
Thanks Shawn
October 21, 2009 at 10:03 am
Hi Shawn
I made a copy of the maint plan that was having issues as you suggested and fixed the schedule
took me a while to straighten out all the constraint lines as they are a mess after you do a copy
it looks like the copying into a new plan may fixed the initial problem
thanks for the tip
Jim
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply