Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sybase long running processes

Posted on 2008-10-01
5
Medium Priority
?
3,852 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 336 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 332 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 332 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

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.

Question has a verified solution.

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

A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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