How to check content of the queue?
Eisen Wang
I'm quite puzzled why it runs so improperly? So I want to check what data
were in the queue
caused block.
But I can't find any command to check queue content in manual.
Is here anybody could help ? Thanks.
--
Best Regards
Wang Jing
Karl Ritter
Can you give us some more details as what you mean by blocked?
Did it stop sending transactions to the replicate DB ?
Did it stop receiving from the Re Agent ?
Did it stop sorting transactions?
When the Rep Server stops working properly, what does admin who_is_down
give you ?
Do you have any interesting error msgs in the errorlog ?
Here are several very useful commands!
-- Make rep server print all SQL send to replicate databases into the error
log
trace 'on', 'dsi', 'dsi_buf_dump'
go
-- after a connection is down, log first tran into the exception table
sysadmin log_first_tran ,<ds>,<db>
go
-- view transactions logged in the exception table
rs_helpexception
rs_helpexception <tran_id>, 'v'
- Karl
"Eisen Wang" <wa...@e-future.com.cn> wrote in message
news:406781ff$2@forums-1-dub...
Fredrik Berglin
>
> Recently the Replication System often gets blocked.
> I'm quite puzzled why it runs so improperly? So I want to check what data
> were in the queue
> caused block.
> But I can't find any command to check queue content in manual.
> Is here anybody could help ? Thanks.
Hi.
First, there's the command "sysadmin log_first_tran" which logs the first transaction in a queue to the RSSD.
Normally, the RepServer doesn't save applied transactions and you can therefore not dump them after they have been applied or skipped.
This can be changed by setting up a save-interval on the queue.
If there is a save interval or there are commands in the queue, the command to dump them is: "sysadmin dump_queue". Look it up in the manual before you use it as it has some rather non-trivial arguments.
Also, you should take a look at "sysadmin dump_file" to be able to redirect the output from the dump command..
Regards.
// Fredrik Berlin
--
------------------------------------------------------
Read the source, Luke.
------------------------------------------------------
Fredrik Berglin fredrik...@commentor.nospam.se
Commentor AB Tel: 031 - 701 19 00
Pusterviksgatan 3-9 Cell: 0707 - 48 64 08
413 01 Göteborg Fax: 031 - 711 51 25
------------------------------------------------------
Eisen Wang
Well, the "block" I said means that the data can't get pass to the
destination ASE through Rep System.
But while this happens , I type "admin health", it always report "health"…
when I use RSSD database ,and type "rs_helppartition ..." , get the report
is the outbound queue nearly full,
and the inbound queue kept clean...
And there's no error message in the errorlog file.
and the rs_helpexception reports the data which skiped and didn't exist in
the queue any more,
while what I want to check is the SQL currently in the queue...
--
Best Regards
Wang Jing
"Karl Ritter" <karl.ritter (at) europe.com> 写入邮件
news:4067d019@forums-2-dub...
Karl Ritter
> is the outbound queue nearly full,
Interesting! To get a better idea, can you post the output of the folloing
-- Run this on the rep server
admin who
go
-- Run this in the RSSD database of the rep server
-- Please post the output
begin
declare @qsize int, @qalloc int, @qfree int
set nocount on
select "DS.DB queue" = convert( char(25), dsname+"."
+ dbname + ' (' + convert( char(1), q_type) + ')'),
'MB' = count(*)
from rs_segments, rs_databases
where used_flag > 0 and q_number=dbid
group by dsname, dbname, q_type
compute sum (count(*))
select @qsize = sum( num_segs), @qalloc = sum( allocated_segs)
from rs_diskpartitions where status = 1
select @qfree = @qsize - @qalloc
print "Total size of queue(s): %1!MB Allocated: %2!MB Free: %3!MB",
@qsize, @qalloc, @qfree
end
go
I need to know which out-bound queue is causing the problem. This would help
get to the real problem
- Karl
"Eisen Wang" <wa...@e-future.com.cn> wrote in message
news:4068cfe8@forums-2-dub...
Eisen Wang
Yesterday night, because I can't stand for this happens again and again. so
I rebuilt the whole Rep System at all.
Now with the new Rep-System, it runs quite well. But I can't provide the
infomation that you needed.
I'm really sorry for that.
However if you want any further infomation ,I'd like to help.news:4069d9ed$1@forums-2-dub...
Jeff Tallman
You need to not only check admin who, but also look at admin who, sqm -
pay particular attention to next.read vs. last seg.block - if they are
close or next.read is higher, you are fine - then look at your save
interval. If they are far apart, then you are having a delivery rate
issue and you probably need to look at parallel DSI's - but first check
out the QP from ASE - do a sp_showplan on the SQL as executed in ASE
...if triggers are on - you may want to turn them off (makeing sure you
are not replicating procs if you do so) or you may want to make the
triggers more optimal for atomic row inserts/updates/deletes.
abhay
queue:
1. Truncate the RSSD..rs_queumsg and RSSD..rs_queuemsgtxt
2. Suspend the connection to DSI ( if not already suspended
)
3. Execute the following command
admin who,SQM
4. Get the Queue#. It is shown under INFO column of the
output of above command. For example, if the INFO column
says 135:0, 135 is the queue number and 0 means outbound
queue. ( 1 means inbound queue ).
5. Obtain the contents of the queue :
sysadmin dump_queue, 135, 0, -2, 1, -2 ,RSSD
135 : Queue number
0 : Outbound queue ( use 1 for inbound queue )
-2: Inactive as well as Active portion of queue
1: start with block 1 of the stable queu
-2: upto end of the queue
rssd: dump it into RSSD
6. Now examine the following tables
select * from rssd..rs_queuemsg
select * from rssd..rs_queuemsgtxt
thanks
abhay
Eisen Wang
--
Best Regards
Wang Jing
<abhay> 写入邮件 news:4075a36c.e6...@sybase.com...
Carel Cornelius
> sysadmin dump_queue, 135, 0, -2, 1, -2 ,RSSD
>
> 135 : Queue number
> 0 : Outbound queue ( use 1 for inbound queue )
> -2: Inactive as well as Active portion of queue
> 1: start with block 1 of the stable queu
> -2: upto end of the queue
> rssd: dump it into RSSD
>
> select * from rssd..rs_queuemsg
>
> select * from rssd..rs_queuemsgtxt
Instead of adding additional stuff to the RSSD that has to be deleted later,
you can also do it directly to the ISQL client, or to a file.
If your queue is very large, with the options you specified, it may fill up
your RSSD which could totally prevent RepServer from working. I suggest
using:
sysadmin dump_queue, 135, 0, -1, -2, 1, client
Setting seg to -1 and blk to -2 starts with the first unread block in the
queue.
If you absolutely have to dump all of the queue, rather output it to a file
by using this sequence:
sysadmin dump_file, 'filename.txt'
go
sysadmin dump_queue, 135, 0, -2, 1, -2
go
sysadmin dump_file
go
Regards
Carel
rajat199...@gmail.com
What all more column more to see to check and verify whether replication is working properly .
=> admin who ,sqm // next.read vs. last seg.block --- I got this one .
Below i did not got ..interval from where to see ?
---------------------------------------------------------------------------