BUG: Fallback to Primary Server May Not Move All Databases BackID: Q176835
|
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.
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.
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.
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 : kbcode SSrvAdmin kbbug6.50
Version : WINNT:6.5
Platform : WINDOWS
Issue type : kbbug Last Reviewed: April 20, 1999