Microsoft KB Archive/176835

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

Article ID: 176835

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q176835



BUG #: 17403 (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

Additional query words: failover failback stproc st_proc st proc

Keywords: kbbug kbcode kbsqlserv650bug KB176835

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.