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

MORE INFORMATION

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