Solved

Sybase long running processes

Posted on 2008-10-01
5
3,331 Views
Last Modified: 2008-11-07
How to find the long running processes among the processes that are running now.
0
Comment
Question by:koppcha
5 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 84 total points
Comment Utility
Easy answer:

select * from sysprocesses

The column physical_io is reset per batch (ie. each "go"). This is often a good clue as to what's been running a while. It's also a good way of checking whether a process is hung - if this column isn't incrementing, then maybe the process is actually hung. (Most "hung" processes are still doing their rollback, which will increase physical_io.)


More complicated answer:

Install the MDA tables, start monitoring statements, query the relevant MDA tables which can tell you the time spent on the current command. (I'm not in front of an ASE but I can get back to you with the exact tables and columns you'd want.)
0
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 83 total points
Comment Utility
Get to know the sysprocesses table and columns.  I would stay with it until you absolutely have to go to MDA.  

Even then, use the stats and wait info, but stay away from statements.  Basically they are very transient: to get anything useful, you have to capture the info and make it persistent, by storing it in your own tables. And the more you capture, the more overhead you will suffer.  For the info you need, sysprocesses columns plus a few more are in monProcesses; join to monProcessActivity on SPID and KPID, for a further no of columns.  Select * for starters.
0
 
LVL 10

Assisted Solution

by:bret
bret earned 83 total points
Comment Utility
Another way is to look at syslogs.

To start with, what we are looking for are beginxacts that have no corresponding endxacts.
The xactid field in syslogs is a binary representation of a pageno, rowno in the log for the begin transaction,  op code 0 is begin xact, op code 30 is end exact
so..

select xactid from syslogs where op = 0 and xactid not in (select xactid from syslogs where op = 30)

gives us a list of the xactids of all open transactions.

We can enhance that code to build up a dbcc log script to get more detail

select "dbcc log(tempdb, 1, "
      + convert(varchar(10), convert(int,xactid))
      + ","
      + convert(varchar(4), convert(smallint, substring(xactid,5,2)))
      + ", 1, 0 )"
      from syslogs
    where op = 0
    and xactid not in
    (select xactid from syslogs where op = 30)

 
That query will generate results that are executable tsql script something like

 -------------------------------------------
 dbcc log(tempdb, 1, 1139,7, 1, 0 )          
 dbcc log(tempdb, 1, 1139,18, 1, 0 )        
 dbcc log(tempdb, 1, 1139,23, 1, 0 )        

(3 rows affected)
Run that script  and you will get the beginxact records for the open transactions, which
gives you spid, uid, and begin time


LOG RECORDS:
        BEGINXACT                (1139,7)       sessionid=1139,7
        attcnt=1 rno=7 op=0 padlen=2 sessionid=1139,7 len=68
        odc_stat=0x0000 (0x0000)
        loh_status: 0x0 (0x00000000)
        masterxsid=(invalid sessionid)
        xstat=XBEG_ENDXACT,
        spid=4 suid=0 uid=1 masterdbid=0 dtmcord=0
        name=$dmpxact   time=Sep 30 2008 11:53:45:430AM


0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now