Looking for a method to find a crashing transaction in the stable queue?

[simon]

When I dump the queue using "sysadmin dump_queue" I see a ton of
transactions
wrapped in begin tran/commit that have nothing to do with the error message.

I take the batching off via 'alter connection set batch to 'off' to make
sure the commits are not purged from the transaction list, but still get the
same result. And the first begin/commit tran never comes through, while the
actual error seems to be sitting somewhere in the middle of the queue.

I can't really afford to resume skip transaction, since I have a lot of
updates that I can't run behind earlier updates.

How have you, gentlemen, been handling these situations? I use a
single-threaded dsi, with batching off, as indicated earlier. The rest of
the rep.settings are default. Environment is ase 12.

Separate question:
Have you ever encountered a dumped stable queue transaction that contained a
syntax
error in the dump file (or RSSD)? One of the calls looked like "update table
where <condition>" with no set clause. I thought it was quite weird. The
rest of transactions were ok.





--- news://freenews.netfront.net/ - complaints: ***@netfront.net ---

[Leonid Gvirtz]

Post by simon
When I dump the queue using "sysadmin dump_queue" I see a ton of
transactions
wrapped in begin tran/commit that have nothing to do with the error message.
I take the batching off via 'alter connection set batch to 'off' to make
sure the commits are not purged from the transaction list, but still get the
same result. And the first begin/commit tran never comes through, while the
actual error seems to be sitting somewhere in the middle of the queue.
I can't really afford to resume skip transaction, since I have a lot of
updates that I can't run behind earlier updates.
How have you, gentlemen, been handling these situations? I use a
single-threaded dsi, with batching off, as indicated earlier. The rest of
the rep.settings are default. Environment is ase 12.
Have you ever encountered a dumped stable queue transaction that contained a
syntax
error in the dump file (or RSSD)? One of the calls looked like "update table
where <condition>" with no set clause. I thought it was quite weird. The
rest of transactions were ok.

Hi Simon

Regarding the queue dump - what parameters for sysadmin dump_queue did
you use? If you dump the whole queue - then it definitely may include
transactions that have been already replicated. This is because the
space in queues is allocated in units of segments (1 MB) and blocks,
related to transactions that have been replicated, are not deallocated
immediately.

It may be more convenient for you to dump the queue into RSSD instead
of a file. Then, you will be able to query rs_queuemsg and
rs_queuemsgtxt with SQL, which provides greater flexibility for your
research. Just try to dump a number of segments into RSSD to see if it
is helpful for you. When you discover all transactions that you want
to get rid of - you can use sysadmin sqm_zap_tran to remove them from
the queue. Actually, I don't quite understand the actual problem you
are trying to solve. Of course, I would prefer the workaround that
doesn't include manual deletion of commands from a queue if possible.

Regarding your second question - I recall that I saw something like
this when tried to replicate an update that updated only fields that
were not included in the table replication definition (for regular
replication). I don't remember the details, though.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

[simon]

Ok, I think I started getting it.

I'm dumping the queue to both the file and RSSD, using

sysadmin dump_queue, <queue_number>, 0, -1, 1, -2, RSSD

However, when getting exactly to the erroneous transaction, you also need to
xref target_DB.dbo.rs_lastcommit.origin_qid column against the
rs_queuemsg.origin_q_id (sic!) to get to the first transaction in the queue
that had not been replicated yet.
Anything below that watermark may have SQL text skewed, therefore you can't
rely on seeing committed tramsaction commands for sure.
That was my confusion initially.

Thank you, Leo, for pushing me in the right direction.

Post by Leonid Gvirtz
Hi Simon
Regarding the queue dump - what parameters for sysadmin dump_queue did
you use? If you dump the whole queue - then it definitely may include
transactions that have been already replicated. This is because the
space in queues is allocated in units of segments (1 MB) and blocks,
related to transactions that have been replicated, are not deallocated
immediately.
It may be more convenient for you to dump the queue into RSSD instead
of a file. Then, you will be able to query rs_queuemsg and
rs_queuemsgtxt with SQL, which provides greater flexibility for your
research. Just try to dump a number of segments into RSSD to see if it
is helpful for you. When you discover all transactions that you want
to get rid of - you can use sysadmin sqm_zap_tran to remove them from
the queue. Actually, I don't quite understand the actual problem you
are trying to solve. Of course, I would prefer the workaround that
doesn't include manual deletion of commands from a queue if possible.
Regarding your second question - I recall that I saw something like
this when tried to replicate an update that updated only fields that
were not included in the table replication definition (for regular
replication). I don't remember the details, though.
Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

--- news://freenews.netfront.net/ - complaints: ***@netfront.net ---