[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sybase reorg, how to check its current progress

Posted on 2011-11-01
7
Medium Priority
?
2,246 Views
Last Modified: 2012-05-12
I have reorg command which now executing, may I know how many indexes have this reorg completed and its current progressing ?
0
Comment
Question by:motioneye
  • 4
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 37062713
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
 

Author Comment

by:motioneye
ID: 37062896
how do we know how many table that has been reorganized ?
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 2000 total points
ID: 37063086
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
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 

Author Comment

by:motioneye
ID: 37063117
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
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 2000 total points
ID: 37063176
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
 

Author Comment

by:motioneye
ID: 37063245
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
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 2000 total points
ID: 37063302
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If anyone asked you to network diagram of the internet, it was drawn in the form of a fluffy cloud which further became known as cloud computing. Popularly cloud computing is defined as workloads that run over the internet in a commercial provider’s…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question