Solved

Sybase long running processes

Posted on 2008-10-01
5
3,626 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase environment variable 1 228
sybase space calculatio, why must divide / multiply by 512 ? 3 138
sybase T-sql different 2 128
sql statement error sybase 2 65
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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