SEO

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?

On Apr 8, 9:25 pm, ran...@sanovi.com 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?

I can't help you get this information, but I'd
love to know why you'd ever need it!

<;-)

On Apr 9, 10:56*am, ThanksButNo <no.no.tha...@gmail.com> wrote:
> On Apr 8, 9:25 pm, ran...@sanovi.com 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?
>
> I can't help you get this information, but I'd
> love to know why you'd ever need it!
>
> <;-)

The easiest way you can do this is to have a look at backup server log
file for both the servers and locate when dump tran /load tran was
happened last.

HTH
Manish Negandhi
[TeamSybase Intern]

Manish,
The backup server log will tell me when the dump was done but not the
timestamp of the last transaction in the dumped file.

As to why anyone would need this, it simply is a more accurate
indication of how much the DR database is behind the production
database.

Thanks for the responses anyway. Let me know if you can be of help for
my original question.
Regards,
Ranjit

On Apr 9, 5:42 am, ran...@sanovi.com wrote:

>
> As to why anyone would need this, it simply is a more accurate
> indication of how much the DR database is behind the production
> database.
>

Why not just set up a scheduled cron process that takes a transaction
dump from the production database every N minutes, then immediately
loads it to the backup database?

What's wrong with just doing this every N minutes, regardless of
whether or not it might actually be "necessary" or "much behind"?

This method guarantees that your backup database will always be up-to-
date within N minutes. Select "N" based on whatever you or your
customer is comfortable with.

I tried to be "clever" once by having the transaction dump occur every
15 minutes during working hours. Then the customer fooled me by coming
in weekends for the month-end closing. The system locked up from the
logs filling up. So my "cleverness" cost me some weekend support, that
I don't get paid extra for. Particularly harrowing was that this
customer is in Singapore and I'm not, so somebody has to wake me at
2AM.

Long story short, I now have it dumping and loading every 15 minutes
for 22 hours of every day of the week, excepting an hour on either
side of midnite when it does a full dump. Maybe wears the disks a
little more, but that's their problem.

:-)

On Apr 8, 10:25 pm, ran...@sanovi.com 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

Bert,
Thanks. Exactly what I was looking for. (1) is new to me. (2) I
already do. There is some parsing work and I dont know when I will
implement but now I have a design.

ThanksButNo,
The dump-load cycle you mentioned is what I do. And weekends can have
high activity. My question was about retreiving the information.

Thanks all,
Ranjit