Role Change Error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Role Change Error

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?

Review information from this KBA http://support.microsoft.com/?id=326485] to resolve the issue. HTH 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.
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.
What are the privileges for SQL service account? 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.
The SQL services on both servers have full admin privilages. Both serveres are W2K SP4 running SQL 2000 Enterprice Edition.
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.
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.
One of the servers is SP3. the Other has the letters RTM in the section where SP details should be. What does RTM mean??
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.
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.
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?

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.
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.
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.
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.
]]>