Sybase reorg, how to check its current progress

I have reorg command which now executing, may I know how many indexes have this reorg completed and its current progressing ?
motioneyeAsked:
Who is Participating?
 
Joe WoodhousePrincipal ConsultantCommented:
If you run sp_who and sp_lock during its progress, you'll be able to get the object_id of whatever it is currently working on.

You can use:

   select object_name(object_id, db_id)

to see where it's up to. Compare that to what it's working on and you'll see where it's up to - remember reorg on its own only works on one object, so you must have a script or procedure that is generating or working with a list of objects and calling reorg on them in order.

You might find it more useful to modify this parent script to tell you more about its progress by writing to some kind of log (or even the Sybase ASE errorlog).
0
 
motioneyeAuthor Commented:
how do we know how many table that has been reorganized ?
0
 
Joe WoodhousePrincipal ConsultantCommented:
Remember, reorg (in any form) only does work on one table.

If you have reorgs being run on multiple objects - and you must, if you're asking which one it's up to - then you need to look at whatever code or script is launching these reorgs. The only way you'll get more than one reorg being run is if something has asked ASE to do that.

That means you have a script or proc or batch file somewhere that is running these reorgs, yes? Which means that script is determining what tables to run it on and in which order.

ASE (and I) can't tell you how many have already been worked on, because ASE only ever sees each reorg statement on its own. Take a look at the script that's running these reorgs - somehow it either has a list of tables to run it on, or is generating that list dynamically.

Once you see what tables it's running on and in what order, knowing which one ASE is currently working on will tell you where it's up to, how many have been completed, and how many are still to go.

This strictly speaking isn't an ASE issue but an issue for that script/code/proc/batch... which is why I suggested you might find it helpful to rewrite it to be more friendly about announcing its progress.  
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
motioneyeAuthor Commented:
Thanks Joe for the explanation, yes the command is somehow reorg dynamically, as of now it stukc with one table almost for 2 hour. so if I kill this sessions will it stop immediately ? I don't expect for any rollback here.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Reorg separately sub-commits its progress regularly so there won't be much to rollback.

However! If the job is actually hung rather than just slow, killing it may not do anything.

When we kill a Sybase ASE process, we are actually telling it "the next time you wake up, kill yourself please". But if it's hung it will never wake up, so it will never actually die.

Try it and see. If it can't be killed (remember to wait a bit for any rollback) then you probably can't get rid of it without restarting ASE.
0
 
motioneyeAuthor Commented:
Btw,
If I look at sysprocesses table and if the reorg is progressing, it shoudl show me highest cpu, and physical_io use ? am I rite here ?
0
 
Joe WoodhousePrincipal ConsultantCommented:
Those numbers are updated over the life of the current connection. If you see them increasing for the spid that's running the reorg, it hasn't hung and is still doing work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.