Solved

Capturing the sql in Sybase

Posted on 2006-11-15
3
1,125 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 500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 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