Solved

Sybase long running processes

Posted on 2008-10-01
5
3,725 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Part One of the two-part Q&A series with MalwareTech.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

627 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