SQL Server Performance Forum – Threads Archive
log shipping role change
NULL<br />Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194 <br />Copyright (C) Microsoft Corporation, 1995 – 1998 <br />NULL<br />USAGE: [-?] | [-S <Server Name>] <br /> [-U <Login ID> [-P <<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword>]] <br /> -D <Database Name> <br /> [-Rpt <Output File>] <br /> [-To <Operator Name>] <br /> [-CkDB | -CkDBNoIdx] <br /> [-CkAl | -CkAlNoIdx] <br /> [-CkTxtAl] <br /> [-CkCat] <br /> [-UpdSts] <br /> [-RebldIdx <% Free Space>] <br /> [-BkUpDB <Backup Path> | -BkUpLog <Backup Path>] <br /> [-BkUpOnlyIfClean] <br /> [-BkUpMedia DISK [-DelBkUps <time period>] | TAPE] <br /> [-UpdOptiStats <% of Database to Sample>] <br /> [-HtmlRpt <Html File>] <br /> [-VrfyBackup] <br /> [-RmUnusedSpace <Minimum DB in MB> <% of free space to leave>] <br /> [-PlanID <GUID identifying the maintenance plan in 36 character hex format>] <br /> [-PlanName <Maintenance plan name>] <br /> [-CrBkSubDir] Create a sub-directory (for storing backup files) for the database(s). <br /> [-UseDefDir] Use the default backup directory. While doing a log shipping role change, I have manullay run the retore job and then i ran the following procedure<br /><br />USE master <br />GO <br />EXEC msdb.dbo.sp_change_secondary_role <br /> @db_name = ‘hotstandby_sec’, <br /> @do_load = 1, <br /> @force_load = 1, <br /> @final_state = 1, <br /> @access_level = 1, <br /> @terminate = 0, <br /> @keep_replication = 0, <br /> @stopat = null <br /><br /><br />It gave me the foll error<br />Server: Msg 22029, Level 16, State 1, Line 0<br />sqlmaint.exe failed.<br /><br />The output of sqlmaint.exe is below<br /><br /> NULL<br />Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194 <br />Copyright (C) Microsoft Corporation, 1995 – 1998 <br />NULL<br />USAGE: [-?] | [-S <Server Name>] <br /> [-U <Login ID> [-P <<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword>]] <br /> -D <Database Name> <br /> [-Rpt <Output File>] <br /> [-To <Operator Name>] <br /> [-CkDB | -CkDBNoIdx] <br /> [-CkAl | -CkAlNoIdx] <br /> [-CkTxtAl] <br /> [-CkCat] <br /> [-UpdSts] <br /> [-RebldIdx <% Free Space>] <br /> [-BkUpDB <Backup Path> | -BkUpLog <Backup Path>] <br /> [-BkUpOnlyIfClean] <br /> [-BkUpMedia DISK [-DelBkUps <time period>] | TAPE] <br /> [-UpdOptiStats <% of Database to Sample>] <br /> [-HtmlRpt <Html File>] <br /> [-VrfyBackup] <br /> [-RmUnusedSpace <Minimum DB in MB> <% of free space to leave>] <br /> [-PlanID <GUID identifying the maintenance plan in 36 character hex format>] <br /> [-PlanName <Maintenance plan name>] <br /> [-CrBkSubDir] Create a sub-directory (for storing backup files) for the database(s). <br /> [-UseDefDir] Use the default backup directory. <br /> [-WriteHistory] Write history to msdb.dbo.sysdbmaintplan_history. <br /> [-DelTxtRpt <time period>] Delete text reports older than given time period <br /><br />Please let me know how to resolve this error.<br /><br />Madhukar <br /> <br />The error handling in this proc is pretty poor. Whatever the problem you nearly always get that message. From experince the problem is usually just a user in the database you are trying to recover. Either check for users or set the @terminate=1. This will kick them. Worse case scenerio and you need to recover the database, check the lastest log backup has been restored by checking the load delta (if it hasn’t force start the restore job) then run: RESTORE DATABASE MyDB WITH RECOVERY You will not be able to reverse roles but your standby database will be usable.
Ensure the account used to start SQLAgent has required privileges on both the servers in order to process the log shipping role. Take help of this KBAhttp://support.microsoft.com/?scid=…/servicedesks/bin/kbsearch.asp?article=288577 to resolve any issues with Maint. plans. 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.
]]>