Cannot Drop Maintanance Plan!!

  • peace2007

    SSCrazy

    Points: 2094

    Hi,

    I had a few maintenance plan defined in SQL2005 server. Then I renamed my Server ( windows and SQLserver). Now, when I want to drop those maintenance plan I'm getting error.

    I have even modified the owner of the related jobs but no success yet

    Could you tell me how I can remove those maintenance plans in such situation?

  • TRACEY-320982

    SSChampion

    Points: 13489

    This thread says you can't

    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297149

    But found this

    1. Select the ID with the select statement

    select * from sysmaintplan_plans

    2. Replace with the selected ID and run the delete statements

    delete from sysmaintplan_log where plan_id = ''

    delete from sysmaintplan_subplans where plan_id = ''

    delete from sysmaintplan_plans where id = ''

    3. Delete the SQL Server Jobs with the Management Studio

    Please check it out first before deleting anything

    here the thread to it

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98114

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Hi,

    It might be similar to an issue I encountered with SQL 2000 ..

    http://sqllearnings.blogspot.com/2009/01/error-14274-cannot-add-update-or-delete.html

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • peace2007

    SSCrazy

    Points: 2094

    1. Select the ID with the select statement

    select * from sysmaintplan_plans

    2. Replace with the selected ID and run the delete statements

    delete from sysmaintplan_log where plan_id = ''

    delete from sysmaintplan_subplans where plan_id = ''

    delete from sysmaintplan_plans where id = ''

    3. Delete the SQL Server Jobs with the Management Studio

    That gave me the answer Thank you 🙂

  • peace2007

    SSCrazy

    Points: 2094

    Mohit (3/7/2009)


    Hi,

    It might be similar to an issue I encountered with SQL 2000 ..

    http://sqllearnings.blogspot.com/2009/01/error-14274-cannot-add-update-or-delete.html

    Mohit.

    I had tried this before but didn't resolve my problem

    Thanks anyway 🙂

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Heh I expected as much; I have yet to rename any SQL 2005 servers. I am sure I'll get around to it; I'll have to keep this little note in mind 🙂 Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Question ...

    Was error similar to this:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Exception has been thrown by the target of an invocation. (mscorlib)

    ------------------------------

    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When

    connecting to SQL Server 2005, this failure may be caused by the fact that

    under the default settings SQL Server does not allow remote connections.

    (provider: Named Pipes Provider, error: 40 - Could not open a connection to

    SQL Server) (Microsoft SQL Server, Error: 53)

    ?

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • peace2007

    SSCrazy

    Points: 2094

    Mohit (3/8/2009)


    Question ...

    Was error similar to this:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Exception has been thrown by the target of an invocation. (mscorlib)

    ------------------------------

    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When

    connecting to SQL Server 2005, this failure may be caused by the fact that

    under the default settings SQL Server does not allow remote connections.

    (provider: Named Pipes Provider, error: 40 - Could not open a connection to

    SQL Server) (Microsoft SQL Server, Error: 53)

    ?

    nope 😉

  • Mohit K. Gupta

    SSChampion

    Points: 12130

    Nuts :p. I guess I'll have to butcher a server LOL thanks for the post :). I made a note about this on me blog so I wouldn't forget it :). Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • TRACEY-320982

    SSChampion

    Points: 13489

    Glad it helped i remembered doing the same thing in SQL 2000 with the same thing so i knew there be something similar in 2005.

  • ed-674698

    Valued Member

    Points: 69

    Thanks much.. fixed my issue:-P

  • george sibbald

    SSC Guru

    Points: 104200

    This is an area in SQL 2005 that just does not hang together as well as it did in SQL 2000, that is copying msdb to another server of a different name (which is effectively what you have done by renaming the server at the OS level). leastways there are no longer quick fixes in this scenario.

    In SQL 2000 you could get round this with 'update msdb..sysjobs set originating_server = 'newname' but I have not found any simple equivalent of this for SQL 2005. Manually deleting plans\jobs via SQL commands and then rebuilding plans from scratch appears to be the only way short of renaming the server to match.

    I have seen cases reported where people have copied msdb over to a new server and attempted to amend\delete plans and have in fact found the actions took effect on the original server, so be warned

    What MS have failed to warn us of in my opinion is the effect this could have on peoples DR plans. There must be a lot of people out there who's DR plans include restoring msdb and have upgraded to SQL 2005, and are in for a nasty surprise if they ever have to failover in anger and run from the DR server for any length of time. If you are on SQL2005 and above and your DR plans are based on restoring system databases to a failover server I suggest the last phase of the process be rename the windows server to that of the primary.

    ---------------------------------------------------------------------

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply