Role Change failed Log Shipping

• Minaz Amin

  SSChampion

  Points: 11052

  More actions

  September 17, 2008 at 4:47 am

  #75318

  Hi Gurus,

  I am in really mess. I was doing a role change from primary to standby server and followed the white paper given by MS. While running the sp_change_monitor_role on the monitor server I received the following error :

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

  Role change failed.

  Query I executed :

  ====================

  EXEC msdb.dbo.sp_change_monitor_role

  @primary_server = 'STANDY\instance2', -- New Primary server

  @secondary_server = 'PRIMARY\instance1', -- old Primary server

  @database = ‘DBTAXCALC',

  @new_source = '\\STANDBY\New_Log_Location'

  This is aproduction server and need to bring this db up.. Need your help urgently.

  Thanks in advance ........

  "More Green More Oxygen !! Plant a tree today"

• Rajesh kasturi

  SSCarpal Tunnel

  Points: 4007

  More actions

  September 17, 2008 at 7:22 am

  #872986

  Could you please try like this

  EXEC msdb.dbo.sp_change_monitor_role @primary_server =

  ' ',@secondary_server =

  ' ',@database =

  ' ',@new_source =

  '\\ \LOGSHIPPING\'

  Just have a look at

  http://www.themssforum.com/SQLServer/spchangemonitorrole-Role/

  Rajesh Kasturi

• Minaz Amin

  SSChampion

  Points: 11052

  More actions

  September 18, 2008 at 2:28 am

  #873440

  Thanks for the link.

  But this did not solve the issue. I removed the maint plan and all log shipping jobs from the primary server and manually added a new pair throught the maint plan from secondary to primary. With this process the monitor server was able to sense both the server.

  "More Green More Oxygen !! Plant a tree today"