On Apr 8, 10:25 pm, ran...@[EMAIL PROTECTED]
wrote:
> For monitoring a sybase disaster recovery site I am trying to get the
> following information from the sybase server:
> 1. Last DUMPED transaction id and its timestamp from an online
> database.
> 2. Latest APPLIED transaction id and its timestamp on a
> database in "online for standby access" state.
> Here, the second database is receiving transaction log files that were
> dumped from the first database server. To provide an example, assume
> someone did "dump transaction <dbname> to 'filename'" two days ago.
> Today I would like to run a query on the sybase data or backup server
> to obtain the last dumped transaction id and its timestamp (and not
> the time of issue of the "dump transaction" command).
>
> I have checked the system tables and some dbcc commands but did not
> get this info. Is there a way to get the above data?
1) Last dumped transaction id and it's timestamp from on online
database.
I assume you really want the last completed transaction? There might
be any number of transaction ids from open transactions included, and
it doesn't really matter which of them was "last".
The following should be close if not exact. A checkpoint is issued as
part of dump tran; the most recent endxact previous to the checkpoint
should be the last committed transaction included in a regular dump.
(dump for standby_access might be considerably further back in the
log).
dbcc traceon(3604)
go
-- List all the checkpoint log records in order from most recent
-- and search output for the first one associated with a dump
transaction
-- it will have the CKPT_DMPXACT_DUMP status
-- (assuming it is among the last 100,000 checkpoints issued)
dbcc log(dbname, 0,0,0, -100000, 17)
go
CHECKPOINT (2565,13) sessionid=2565,13
<---------------
attcnt=1 rno=13 op=17 padlen=0 sessionid=2565,13 len=60
odc_stat=0x0000 (0x0000)
loh_status: 0x0 (0x00000000)
rows=0, pages=0 extents=0 logvers=6
timestamp=0x0000 0x00000a10 xstat=0x0003 (0x0002
(CKPT_HASACTIVE), 0x0001
(CKPT_DMPXACT_DUMP))
<----------------------------------------------------
time=Apr 9 2008 11:37AM
oldest active xact marker: (2565,12)
-- extract the session id (a,b) from that log record
-- search the starting at that record backwards for the previous
-- ENDXACT (op 30) record
-- dbcc log(dbname, -1, <a>, <b>, -1, 30)
dbcc log(dbname, -1, 2565,13, -1, 30)
ENDXACT (2565,11) sessionid=2565,9
attcnt=1 rno=11 op=30 padlen=0 sessionid=2565,9 len=28
odc_stat=0x0000 (0x0000)
loh_status: 0x0 (0x00000000)
endstat=COMMIT time=Apr 9 2008 11:36:39:116AM
xstat=0x0 []
-- extract the sessionid and the "time" field.
-- Those are the values you are looking for
2) > Latest APPLIED transaction id and its timestamp on a
> database in "online for standby access" state.
Easy. Just find the most recent ENDXACT record from the log.
dbcc traceon(3604)
go
dbcclog (offlinedbname, 0,0,0,-1,30)
go
|
