Solved

Sybase long running processes

Posted on 2008-10-01
5
3,529 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
ID: 22615960
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
ID: 22616272
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
ID: 22633867
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Some of the SEO trends we might expect in 2017.
This article describes how to reset your Windows 10 password when you've forgotten it.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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