"Orphaned" maintenance plan

Warning ahead of time - I'm a total idiot about this stuff.
Server is Windows Server 2003
SQL Enterprise Manager version is 8.0
I had been having a problem getting backups to auto delete and so
followed the instructions in a Microsoft KB article and set the
recovery mode to Full for all of the DBs. That solved the problem for
almost every database in the system.
However, apparently at some point several of the databases used a
maintenance plan called "DB Maintenance Plan2" which no longer exists
on the system. (There is currently only a single maintenance plan on
the system.) How can I find out where this is coming from and delete
it?
This was the error in the Events log:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
The Job was invoked by Schedule 5 (Schedule 1). The last step to run
was step 1 (Step 1).
Please talk in baby steps - thanks.
JulieHi Julie
"Kaidi" wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used a
> maintenance plan called "DB Maintenance Plan2" which no longer exists
> on the system. (There is currently only a single maintenance plan on
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie
>
I am not sure if it is the job you are looking for or not! But if you start
SQL Enterprise Manager, and open up the Node for the SQL Server Instance, you
will see a node for management under which is SQL Server Agent and then Jobs.
There should be a job called "DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan2' " which will have a red cross next to it because it has
failed. Right click the job and choose delete from the menu to remove the
job. Usually when a maintenance plan is deleted the associated jobs should be
deleted.
John|||Followup, for anyone who happens to hit this post on a usenet search.
(I hate finding my problem with no solution.
There's an SP called sp_delete_job that you can use with either a job
name or a job id and it takes care of the cleanup required to remove
the maintenance plan completely from your system.
Do *not* simply delete the job rows that are causing the problem from
the msdb.sysjobs table. Unfortunately, that is what I did, and it's
taken me a couple of hours to track down all of the pieces/references
to the job in other tables. I'm just hoping to heck I got them all...I
went manually through all of the delete commands in the sp_delete_job
procedure, and praying that it worked!
:P
Julie
On Apr 4, 12:04 pm, "Kaidi" <julie.sie...@.gmail.com> wrote:
> Warning ahead of time - I'm a total idiot about this stuff.
> Server is Windows Server 2003
> SQL Enterprise Manager version is 8.0
> I had been having a problem getting backups to auto delete and so
> followed the instructions in a Microsoft KB article and set the
> recovery mode to Full for all of the DBs. That solved the problem for
> almost every database in the system.
> However, apparently at some point several of the databases used amaintenance plancalled "DB MaintenancePlan2" which no longer exists
> on the system. (There is currently only a singlemaintenance planon
> the system.) How can I find out where this is coming from and delete
> it?
> This was the error in the Events log:
> SQL Server Scheduled Job 'DB Backup Job for DBMaintenance Plan'DB
> MaintenancePlan2'' (0x8AB900BBC4CB7E409DCF47CACB85233E) - Status:
> Failed - Invoked on: 2007-04-04 03:00:00 - Message: The job failed.
> The Job was invoked by Schedule 5 (Schedule 1). The last step to run
> was step 1 (Step 1).
> Please talk in baby steps - thanks.
> Julie|||Hi Julie
"Kaidi" wrote:
> Followup, for anyone who happens to hit this post on a usenet search.
> (I hate finding my problem with no solution.
I had replied to this!
> There's an SP called sp_delete_job that you can use with either a job
> name or a job id and it takes care of the cleanup required to remove
> the maintenance plan completely from your system.
> Do *not* simply delete the job rows that are causing the problem from
> the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> taken me a couple of hours to track down all of the pieces/references
> to the job in other tables. I'm just hoping to heck I got them all...I
> went manually through all of the delete commands in the sp_delete_job
> procedure, and praying that it worked!
You did not mention this in your original post. It is never recommended to
hack the system tables.
> :P
> Julie
>
John|||On Apr 7, 6:56 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Julie
> "Kaidi" wrote:
> > Followup, for anyone who happens to hit this post on a usenet search.
> > (I hate finding my problem with no solution.
> I had replied to this!
>
No replies came up in this thread at Google, John - sorry!
> > Do *not* simply delete the job rows that are causing the problem from
> > the msdb.sysjobs table. Unfortunately, that is what I did, and it's
> > taken me a couple of hours to track down all of the pieces/references
> > to the job in other tables. I'm just hoping to heck I got them all...I
> > went manually through all of the delete commands in the sp_delete_job
> > procedure, and praying that it worked!
> You did not mention this in your original post. It is never recommended to
> hack the system tables.
> John
When I posted originally, I hadn't DONE that yet, but didn't see any
replies.
There are 3 dbs that are backing up through the orphan, at 1gb each
when squished as small as they can go - the HD fills up and if I miss
deleting them, it shuts my client's biz down entirely. I'm gonna be
gone for a month, and they clueless with regards to the server and
have no IT person, so I had to do SOMETHING. Obviously, that something
wasn't a great choice! (Wish I'd seen whatever your post was - lol)
While the orphan is still generating a couple of "Unable to retrieve
steps" errors in the event log, it's at least no longer creating
backups. I think the job order or whatever that'd be called (hey, I'm
a graphic designer and a hack vbscript/javascript programmer - lol)
must be cached somewhere. I'll track it down eventually.
You have *no idea* how sorry I am that I haven't seen your reply! :D
Thanks again,
Julie