dump/load tran with standby_access problems

[unknown]

Hi,

We are trying to set up a standby database for our
production server via the 'standby_access'-method.

Once a day the database is dumped, and then every 10 minutes
the transaction logs are dumped (with standby_access).

Loading the database works fine, we online it for
standby_access, but loading the dumped transaction logs give
some problems.


-----------------
Production:
Adaptive Server Enterprise/12.5.3/EBF 13061 ESD#5/P/NT
(IX86)/OS 4.0/ase1253/1939/32-bit/OPT/Tue Dec 06 00:21:31
2005

Standby:
Adaptive Server Enterprise/15.0/EBF 13248 ESD#1 ONE-OFF/P/NT
(IX86)/Windows 2000/ase150eap/2170/32-bit/OPT/Wed Jan 18
02:46:58 2006
-----------------


--------------------
1> load tran Department
2> from "D:\copy-and-load\1250_DEPARTMENT.trn"
3> go
Backup Server session id is: 22. Use this value when
executing the
'sp_volchanged' system stored procedure after fulfilling any
volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'ARTMENT060870B47A'
section number 1
mounted on disk file 'D:\copy-and-load\1250_DEPARTMENT.trn'
Backup Server: 4.58.1.1: Database Department: 10 kilobytes
LOADed.
Backup Server: 3.42.1.1: LOAD is complete (database
Department).
The end of this transaction dump (134951, 20) was earlier
than the current last
log marker in the database (138590, 6) so the only effect of
this LOAD
TRANSACTION command was to enable the load of the subsequent
transaction dump.
ONLINE DATABASE FOR STANDBY_ACCESS cannot be performed on
database 'Department'
until a subsequent standby_access transaction dump has been
loaded.
1> online database Department for standby_access
2> go
Started estimating recovery log boundaries for database
'Department'.
Database 'Department', checkpoint=(138581, 0),
first=(138581, 0), last=(138590,6).
Completed estimating recovery log boundaries for database
'Department'.
Started ANALYSIS pass for database 'Department'.
Completed ANALYSIS pass for database 'Department'.
Recovery of database 'Department' will undo incomplete
nested top actions.
Database 'Department' is now online.
1>
-------------------

It seems that it may have worked when onlining the database
manually, but testdata is not replicated.

The message about the log marker is with every load tran the
same, including numbers (134951, 20) (138590, 6).

Transaction log dumps are loaded in the correct sequence.



I was also wondering why the database can't have open
connections when loading transaction dumps. In our case
there will be 1 connection that reads data. Is there a way
to allow this?

------------------
1> load tran Department
2> from "D:\copy-and-load\1300_DEPARTMENT.trn"
3> go
Msg 4301, Level 16, State 2:
Server 'ERATO62', Line 1:
Database in use. A user with System Administrator (SA) role
must have exclusive
use of DB to load transaction.
1>
-----------------

Any comments are appreciated,
Koen De Proft

[unknown]

Hi again,

I've noticed that there is a problem when dumping the
transaction logs
with standby_access. The dump files remain the same 22 KB
that they
were in the first dump tran, whatever action going on in the
database.

When I leave the 'with standby_access' option away,
transaction logs
are dumped just fine.

It is as if there is an ongoing transaction running that
won't end,
because 'with standby_access' specifies that only completed
transactions are to be dumped. The dump continues to the
furthest
point it can find at which a transaction has just completed
and there
are no other active transactions (cfr SAG).

There are no user transactions running, so I don't
understand this
behaviour.

Does anyone have an idea what's going wrong ? Maybe how I
discover the
transaction that's hampering me ?

Thanks in advance,
Koen De Proft

[Bret Halford]

From the original messages, it sounds like the tran dump
was a subset of the syslogs that was already in the original
dump database. Until additional log records get into the tran
dump, there is nothing more to apply to the database. That isn't
a bad thing in itself - it just means that no transactions committed
between
the dump database and the dump tran.

Does the master..syslogshold table show anything?

Having an active-but-not-moving replication lot transfer marker
could cause this.

How many records are in the log (on the source side). Is this growing?

The database can't be in use as you load the tran dump. A solution
might
be to have that one process also be the one that loads the tran dumps -
the
process would be do reporting, use <any other database>, load tran
dump(s),
online for standby_access, use database, do reporting.

Alternatively, the process could just stay in some other database and
just
access tables with fully qualified names:

use otherdatabase
go
select * from mydatabase..mytable
go

Loading every ten minutes seems rather aggressive to me - replication
might be a better
option for you if you want to keep the standby that up-to-date - and it
would allow
the reporting connection to stay in place full time.

-bret

Post by unknown
Hi again,
I've noticed that there is a problem when dumping the
transaction logs
with standby_access. The dump files remain the same 22 KB
that they
were in the first dump tran, whatever action going on in the
database.
When I leave the 'with standby_access' option away,
transaction logs
are dumped just fine.
It is as if there is an ongoing transaction running that
won't end,
because 'with standby_access' specifies that only completed
transactions are to be dumped. The dump continues to the
furthest
point it can find at which a transaction has just completed
and there
are no other active transactions (cfr SAG).
There are no user transactions running, so I don't
understand this
behaviour.
Does anyone have an idea what's going wrong ? Maybe how I
discover the
transaction that's hampering me ?
Thanks in advance,
Koen De Proft

[unknown]

Hi Bret,

I'm sorry I reply this late.

We have given up on that solution, we also found it too
aggressive, and it would cause us problems in the future. We
have moved to Replication Server (although that doesn't go
without problems, too - see the repserver newsgroup).

Thank you for your comments, though. They did give me more
insight in the problem.

Best Regards,
Koen

Post by Bret Halford
From the original messages, it sounds like the tran dump
was a subset of the syslogs that was already in the
original dump database. Until additional log records get
into the tran dump, there is nothing more to apply to the
database. That isn't a bad thing in itself - it just
means that no transactions committed between
the dump database and the dump tran.
Does the master..syslogshold table show anything?
Having an active-but-not-moving replication lot transfer
marker could cause this.
How many records are in the log (on the source side). Is
this growing?
The database can't be in use as you load the tran dump. A
solution might
be to have that one process also be the one that loads the
tran dumps - the
process would be do reporting, use <any other database>,
load tran dump(s),
online for standby_access, use database, do reporting.
Alternatively, the process could just stay in some other
database and just
use otherdatabase
go
select * from mydatabase..mytable
go
Loading every ten minutes seems rather aggressive to me -
replication might be a better
option for you if you want to keep the standby that
up-to-date - and it would allow
the reporting connection to stay in place full time.
-bret