Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Capturing the sql in Sybase

Posted on 2006-11-15
3
Medium Priority
?
1,137 Views
Last Modified: 2011-10-03
We are running Sybase 12.5.2 version on Sun Solaris .
There are some processes that run against the a particular database that cause the CPU to
spike upto 100% form an hour every morning .
How would I capture the sql in the database  that causes that to happen in Sybase ,is there a
script I can use or a tool
Appreciate all your help ,
thanks,Gyans
0
Comment
Question by:gyans
3 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 1500 total points
ID: 17949492
You should install the MDA (Monitoring Data Access) tables and configure the monitoring system to capture the SQL_TEXT, among other things.

Rob Verschoor has put together a pretty complete set of documentation and presentations about MDA at:
http://www.sypron.nl/mda.html

The long and short is that you can set things up to capture all of the SQL Text sent to the server though it does not work on RPC calls to stored procedures.  Because there are limited buffer sizes for the data, you will have to create a permanent table and set up a process to do a period INSERT/SELECT from the MDA table to the permanent table so you don't loose anything.

One other thing you could try is using RIBO.  It is a utility that sits between the clients and the servers and captures and decodes TDS protocol network traffic.  It works for smaller sites but if you have a big installation, I would go with the MDA tables.

Regards,
Bill
0
 
LVL 2

Expert Comment

by:RahamanM
ID: 17985909
you can do following

dbcc trace on 3604
go
dbcc sqltext(<spid no>)
go
0
 
LVL 3

Expert Comment

by:pg_vinod
ID: 18074213
if you are not interested in MDA table then
you can store snapshot of sysprocesses in mon_table for every 15 sec in loop.
this can be used for postmortem. you just need to query the mon_table by date and time window of CPU high.
of course this will not have SQL text but you will have an idea what its was doing like exec /insert/update/open cursor delete etc.

hope this will help you.

--Vinod Pottekkatt
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Do online retailers and e-commerce sites even need to bother with the season? Is it lucrative to update and prepare your site for Black Friday and Cyber Monday shoppers? We'll find out now.
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

783 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