BUG: Fallback to Primary Server May Not Move All Databases Back

• Microsoft SQL Server version 6.5

SYMPTOMS

Running the sp_fallback_deactivate_svr_db stored procedure, which attempts to move the databases back to the primary server, may fail to move all databases back if the dbid from the primary server and fallback server do not match.

WORKAROUND

To work around this problem, re-create the sp_fallback_deactivate_svr_db stored procedure with the code provided in the MORE INFORMATION section of this article.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The script to re-create the sp_fallback_deactivate_svr_db stored procedure is provided below. Note that you must be in the master database when running this script.

   if exists (select * from sysobjects where id =
   object_id('dbo.sp_fallback_deactivate_svr_db') and sysstat & 0xf = 4)
      drop procedure dbo.sp_fallback_deactivate_svr_db
   GO

   create procedure sp_fallback_deactivate_svr_db  --1996/02/29 11:06
       @pPrimarySvrName    character varying(30)   = null
      ,@pDbNamePattern    character varying(44)   = '%'
   as
   /********1*********2*********3*********4*********5*********6*********7**
         This sproc should be executed on the fallback server
   (no RPC).
         This sproc will delete rows from system tables, and make
   a will update the spt_fallback_% tables.
         As far as possible, this sproc is designed to overcome
   small activation discrepancies and achieve the intended result.
   *********1*********2*********3*********4*********5*********6*********7*/

   Declare
          @RetCode                  integer

   IF (@@trancount > 0)
      begin
      RaisError(15002,-1,-1,'sp_fallback_deactivate_svr_db')
      Select @RetCode = @@error
      GOTO LABEL_86_RETURN
      end

   Set implicit_transactions     off

   IF (@@trancount > 0)
      begin
      COMMIT TRANSACTION   -- Decrements @@trancount by 1.
      end

   Set nocount                   on
   Set ansi_nulls                on
   Set cursor_close_on_commit    off
   Set xact_abort                off

   Declare
          @ExecRC                   integer
         ,@_error                   integer
         ,@ProcStartDtTm            datetime
         ,@Int1                     integer
         ,@BitDbOffline            integer

   Declare
          @xfallback_dbid           smallint

         ,@db_name                 character varying(30)
         ,@db_dbid                 smallint

   -----------------------------------

   Select
          @RetCode                  = 0
         ,@ProcStartDtTm            = getdate()
         ,@BitDbOffline            = 512


   ----------------------  only SA  --------------------------------


   IF (suser_id() <> 1)  -- must be SA
      begin
      RaisError(15003,-1,-1)
      Select @RetCode = @@error
      GOTO LABEL_86_RETURN
      end

   ----------------------  Need to lookup one primary svr?  --------------


   IF (@pPrimarySvrName is null)
      begin

      SELECT    @Int1   = count(distinct db.xserver_name)
         from   master..spt_fallback_db  db
         where  db.xfallback_dbid        is not null

      IF (@Int1 <> 1)
         begin
         RaisError(15344,-1,-1,@Int1)
         Select @RetCode = @@error
         GOTO LABEL_86_RETURN
         end

      SELECT    @pPrimarySvrName = min(db.xserver_name)
         from   master..spt_fallback_db  db
         where  db.xfallback_dbid        is not null

      end

   ------------------  Handle db parm  ------------------------

   IF (@pDbNamePattern is null)
      Select @pDbNamePattern = '%'


   ------  Fallback already currently deactivated for the primary server?

   IF not exists (SELECT * from  master..spt_fallback_db db
                       where db.xfallback_dbid is not null
                       and   db.xserver_name = @pPrimarySvrName
                       and   db.name      like @pDbNamePattern
                 )
      begin
      RaisError(15353,-1,-1,@@servername,@pPrimarySvrName)
      Select @RetCode = @@error
      GOTO LABEL_86_RETURN
      end


   -----------  Calculate a list of possible sysdev's to delete  ----------


   ---- Capture devs containing to-be-deact dbs.
   Create table #1dev_deact
      (low             integer     not null
      ,xfallback_low   integer     not null
      ,name            varchar(30) not null
      )

   INSERT into #1dev_deact
                  (low ,xfallback_low ,name)
      SELECT
            distinct
                   dev.low ,dev.xfallback_low ,dev.name
            from   master..spt_fallback_dev  dev
                  ,master..spt_fallback_usg  usg
                  ,master..spt_fallback_db  db
            where  db.dbid           = usg.dbid
            and    usg.vstart between dev.low and dev.high
            and    db.xserver_name = usg.xserver_name
            and    db.xserver_name = dev.xserver_name
            and
                   db.xserver_name = @pPrimarySvrName
            and    db.name      like @pDbNamePattern
            and    db.xfallback_dbid   is not null
            and    usg.xfallback_vstart is not null
            and    dev.xfallback_low    is not null


   ---- Capture devs containing dbs other than dbs to-be-deact.
   Create table #2sysdev_othersuse
      (low        integer     not null
      ,name       varchar(30) not null
      )

   INSERT into #2sysdev_othersuse
                  (low ,name)
      SELECT
            distinct
                   sysdev.low ,sysdev.name
            from   master..sysdevices        sysdev
                  ,master..sysusages         sysusg
                  ,master..sysdatabases      sysdb
            where  sysdb.dbid      = sysusg.dbid
            and    sysusg.vstart between sysdev.low and sysdev.high
            and    not exists
                     (SELECT      *
                        from      master..spt_fallback_db  db
                        where     db.xserver_name    = @pPrimarySvrName
                        and       db.name      like    @pDbNamePattern
                        and       db.xfallback_dbid  is not null
                        and       db.xfallback_dbid  = sysdb.dbid
                     )


   ---- Remove some devs from the list of devs to-be-deact.
   DELETE          #1dev_deact
         where     xfallback_low in
                     (SELECT low from #2sysdev_othersuse
                     )

   ---------------------------  Establish cursors  -----------------------


   -------- csr sysdb

   DECLARE
                   csr_11_db
                   insensitive
               cursor for
      SELECT
                   db.xfallback_dbid
                  ,db.dbid
                  ,db.name
         from
                   master..spt_fallback_db     db
         where
                   db.xserver_name    = @pPrimarySvrName
         and       db.name         like @pDbNamePattern
         and       db.xfallback_dbid  is not null
      FOR read only


   ---------------------  Take each relevant db offline  ------------------


   OPEN csr_11_db


   WHILE ('11a'='11a')
      begin


      FETCH
                   next
         from
                   csr_11_db
         into
                   @xfallback_dbid
                  ,@db_dbid
                  ,@db_name

      IF (@@fetch_status <> 0)
         begin
         Close csr_11_db  -- Will use this again.
         BREAK
         end

   ----------

      IF not exists   (
            SELECT       *
               from      master..sysdatabases   sysdb
               where     sysdb.dbid = @xfallback_dbid
               and       sysdb.status & @BitDbOffline = 0  --currently is
                         online
                     )
         begin

         CONTINUE

         end

      Execute @ExecRC = sp_dboption @db_name ,'offline' ,'true' --Also
      defers unshared sysdev.

      IF (@ExecRC <> 0)
         begin
         RaisError(15355,-1,-1,@db_name)
         Select @RetCode = @@error
         GOTO LABEL_86_RETURN
         end

      end -- loop 11a db

   ---------------------------------------------- txn

   BEGIN TRANSACTION  -- @@trancount++;


   ---------------------  Delete sys% tables rows  ---------------


   OPEN csr_11_db


   WHILE ('11b'='11b')
      begin


      FETCH
                   next
         from
                   csr_11_db
         into
                   @xfallback_dbid
                  ,@db_dbid
                  ,@db_name

      IF (@@fetch_status <> 0)
         begin
         Close csr_11_db
         BREAK
         end


   -------- Handle usg.

      DELETE       master..sysusages
            where
                   dbid    = @xfallback_dbid


      UPDATE       master..spt_fallback_usg
            set
                   xdttm_last_ins_upd  = @ProcStartDtTm
                  ,xfallback_vstart    = null
            where
                   xserver_name        = @pPrimarySvrName
            and    dbid                = @db_dbid


   -------- Handle db.

      DELETE       master..sysdatabases
            where
                   dbid    = @xfallback_dbid


      UPDATE       master..spt_fallback_db
            set
                   xdttm_last_ins_upd  = @ProcStartDtTm
                  ,xfallback_dbid      = null
            where
                   xfallback_dbid      = @xfallback_dbid

      end -- loop 11b db


   --------------------  Del all corresponding dev  ---------------------


   ---- Del sysdevs whose children were all just deactivated.

   DELETE       master..sysdevices
         where
                name in
                  (SELECT name from #1dev_deact)
         and    low  in
                  (SELECT xfallback_low from #1dev_deact)
         and    not exists
                  (SELECT      *
                     from      master..sysusages   sysusg
                     where     sysusg.vstart between
                                 master..sysdevices.low  and
                                 master..sysdevices.high
                  )

   UPDATE       master..spt_fallback_dev
         set
                xdttm_last_ins_upd  = @ProcStartDtTm
               ,xfallback_low       = null
         where
                xserver_name        = @pPrimarySvrName
         and    xfallback_low  is not null
         and
                low in (SELECT low from #1dev_deact)


   ------------------------  Finalization  -------------------------


   ----------  Re-Verify integrity, before commit

   Execute @ExecRC = sp_fallback_MS_verify_ri

   IF (@ExecRC <> 0)
      begin
      RaisError(15352,-1,-1,'deactivate s d 2')  -- Do NOT internationalize
      these.
      Select @RetCode = @ExecRC
      Execute sp_fallback_help
      Rollback Transaction
      GOTO LABEL_86_RETURN
      end


   COMMIT TRANSACTION


   Raiserror(15356,-1,-1,@pPrimarySvrName)


   LABEL_86_RETURN:

   Deallocate csr_11_db

   IF (object_id('tempdb..#1dev_deact') is not null)
               drop table #1dev_deact
   IF (object_id('tempdb..#2sysdev_othersuse') is not null)
               drop table #2sysdev_othersuse

   Return @RetCode

   GO

   GRANT  EXECUTE  ON dbo.sp_fallback_deactivate_svr_db  TO public
   GO