SQL Server Performance

Role Change Error

Discussion in 'SQL Server Log Shipping' started by Baire, Apr 27, 2004.

  1. Baire New Member

    I've come across a problem performing role change in Log shipping. I'm following the instruction on BOL and when executing the SP_change_secondary_role, the following error message is displayed:

    Server: Msg 22029, Level 16, State 1, Line 0
    sqlmaint.exe failed.

    Can anyone help on what this message means?
  2. satya Moderator

  3. Baire New Member

    I've looked at the article and the job created with the Database Maintenance Plan wizard completes successfully. My problem is when I run the following script the error message is displayed:

    exec msdb.dbo.sp_change_secondary_role
    @db_name = 'dbname',
    @do_load = 1,
    @force_load = 1,
    @final_state = 1,
    @access_level = 1,
    @terminate = 1,
    @stopat = NULL
    Go

    The database name on both the Primary and Secondary server are the same and I'm logged on as admin when running the script.
  4. satya Moderator

  5. Baire New Member

    The SQL services on both servers have full admin privilages. Both serveres are W2K SP4 running SQL 2000 Enterprice Edition.
  6. Luis Martin Moderator

    Both SQL 2000 same SP?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  7. satya Moderator

    The possibility of this error is due to:
    If this system stored procedure fails because SQL Server cannot obtain exclusive access to the standby database, rerun this system stored procedure after closing any existing connections to the production database. If the first execution of this system stored procedure fails, the log-shipping copy job is still disabled.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. Baire New Member

    One of the servers is SP3. the Other has the letters RTM in the section where SP details should be. What does RTM mean??
  9. satya Moderator

    RTM means Release To Manufacturing and that means with no service pack.
    This could be one of the reason its failing, ensure to maintain all the service pack levels similar between servers.
    (good catch Luis)

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. satya Moderator

    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37 a handy link to keep a track of SQL server versions.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. Baire New Member

    Many thanks; the error was due to the Service Pack being different on both servers. I have now encountered another problem when running the next script (SP_Change_Monitor_role). It returns the following error message:

    Server: Msg 14442, Level 16, State 1, Procedure sp_change_monitor_role, Line 18
    Role change failed

    I've checked the script and it's correct. Any ideas?
  12. satya Moderator

    The stored procedure sp_resolve_logins, has to be run in the master database or it will fail.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. Baire New Member

    I've run the SP_resolve_logins SP and it was successful. I still get the Role Change failed error message when I run the SP_Chanage_Monitor_role SP.
  14. satya Moderator

    Firstly confirm whether you've followed the steps defined in books online about role changes, refer to How to set up and perform a log shipping role change (Transact-SQL) topic for more information.
  15. Baire New Member

    That the document I've been following so far. I see from this forum that someone previously logged a call experiencing the same problem. He/She stated that this was a bug within SQL. I've tried his/her's workaround but it still throws out the same error.

Share This Page