SQL Server Performance

log shipping role change

Discussion in 'SQL Server Log Shipping' started by mgole, Mar 17, 2005.

  1. mgole New Member

    NULL<br />Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194 <br />Copyright (C) Microsoft Corporation, 1995 - 1998 <br />NULL<br />USAGE: [-?] | [-S &lt;Server Name&gt;] <br /> [-U &lt;Login ID&gt; [-P &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword&gt;]] <br /> -D &lt;Database Name&gt; <br /> [-Rpt &lt;Output File&gt;] <br /> [-To &lt;Operator Name&gt;] <br /> [-CkDB | -CkDBNoIdx] <br /> [-CkAl | -CkAlNoIdx] <br /> [-CkTxtAl] <br /> [-CkCat] <br /> [-UpdSts] <br /> [-RebldIdx &lt;% Free Space&gt;] <br /> [-BkUpDB &lt;Backup Path&gt; | -BkUpLog &lt;Backup Path&gt;] <br /> [-BkUpOnlyIfClean] <br /> [-BkUpMedia DISK [-DelBkUps &lt;time period&gt;] | TAPE] <br /> [-UpdOptiStats &lt;% of Database to Sample&gt;] <br /> [-HtmlRpt &lt;Html File&gt;] <br /> [-VrfyBackup] <br /> [-RmUnusedSpace &lt;Minimum DB in MB&gt; &lt;% of free space to leave&gt;] <br /> [-PlanID &lt;GUID identifying the maintenance plan in 36 character hex format&gt;] <br /> [-PlanName &lt;Maintenance plan name&gt;] <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 &lt;Server Name&gt;] <br /> [-U &lt;Login ID&gt; [-P &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword&gt;]] <br /> -D &lt;Database Name&gt; <br /> [-Rpt &lt;Output File&gt;] <br /> [-To &lt;Operator Name&gt;] <br /> [-CkDB | -CkDBNoIdx] <br /> [-CkAl | -CkAlNoIdx] <br /> [-CkTxtAl] <br /> [-CkCat] <br /> [-UpdSts] <br /> [-RebldIdx &lt;% Free Space&gt;] <br /> [-BkUpDB &lt;Backup Path&gt; | -BkUpLog &lt;Backup Path&gt;] <br /> [-BkUpOnlyIfClean] <br /> [-BkUpMedia DISK [-DelBkUps &lt;time period&gt;] | TAPE] <br /> [-UpdOptiStats &lt;% of Database to Sample&gt;] <br /> [-HtmlRpt &lt;Html File&gt;] <br /> [-VrfyBackup] <br /> [-RmUnusedSpace &lt;Minimum DB in MB&gt; &lt;% of free space to leave&gt;] <br /> [-PlanID &lt;GUID identifying the maintenance plan in 36 character hex format&gt;] <br /> [-PlanName &lt;Maintenance plan name&gt;] <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 &lt;time period&gt;] Delete text reports older than given time period <br /><br />Please let me know how to resolve this error.<br /><br />Madhukar <br /> <br />
  2. simondm New Member

    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.
  3. satya Moderator

Share This Page