Query Running for Long time!!

Hi,

I have a C++ prog that runs a set of quries for certain records. Wen I ran the exe, it ran fine for some records and got stuck at a particular record. It got stuck at a certain query and when I tried to run it in SQL Advantage, it was running on & on. I checked the SPIDs for that process and this is the following i got:

dbname          login      spid status pgm      blk cmd       time_blk
 --------------- ---------- ---- ------ -------- --- --------- -------- --------------
 XYZ             ABC        45   recv s          0   AWAITING  ::      
 XYZ             ABC        17   lock s          45  SELECT    10:12:20

When the DB killed the SPID 45, Rest of the quries ran fine. Why is it happening? Please give any suggestion ASAP. This is very critical for our project!!
hkprsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ispalenyCommented:
Very critical and 50 points ?
Deadlock of asynchronous query ?
What SQL server ?
Post SQL code. Interface used. Connection settings.
0
hkprsAuthor Commented:
I am using Sybase 12.5 as the Database. I have the C++ programs on Unix Machine and I am using Rogue Wave library to connect to DB thru C++.

I have a set of records and a set of queries. I have lots of queries that I can't list here. But I have a good number selects, inserts & updates on more than 5 tables. These queries are run for each of the contracts. Each set of queries is run between begin and commit.

When I ran the C++ prog first time it got stuck at one record (Say ABC). After killing the job when I ran the exe again, its processed ABC and stuck after some other contracts!!! after a couple of runs, all the records got processed.

When I tried to check the locks using "sp_locks" I could see the first SPID acquiring locks on all tables.

I know theses many details and can furnish further on request. Please send me some kind of hint of what's happening.

Thanks in advance.

Regards,

0
ornicarCommented:
You could know where it is stuck and which statement(s) don't work by logging the statements to a log file. This could help to locate where is the problem.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gletiecqCommented:
Run dbcc checkalloc on your database and dbcc tablealloc on the table in question.  Thrashing like this on a simple query might be an allocation problem.  If the database comes up clean, see if you can figure out what particular query is causing the promblem and see what the optimizer is trying to do with a "set showplan on" and set statistics on.  If something is showing unusual I/O costs, do an UPDATE STATISTICS on that table or tune the query.

If all that doesn't work, post the individual query here and some info about the tables it's working on.

Greg
0
hkprsAuthor Commented:
Thanks for the response,
I'll try doing all that has been mentioned. Meanwhile I am providing some more infogrmation on this.

I am logging the statements into the log file and I have noticed a strange behaviour. When I ran the prog for 3 records say A, B & C; in the log file its shows that A & B have been processed while the program got stuck during processing the record C. (The set of queries being executed for all the three records is same.)

 I checked the tables that each of the records updates and found that only record A completed the process and updated the tables corectly. Though the log file shows that Record B is processed, when I ran the query to check the fields, the query got stuck. When I killed the prog and checked the tables for the record B, its not updated anything!!!

I am not able to understand anything here. Please help me in this regard.

The same program used to work perfectly fine for hundreds of records earlier. Once the Database has been refreshed with a new dump this problem started. Is it related to anykind of settings done at time of refresh?

Waiting eargerly for some response...
Thanks in advance.

0
gletiecqCommented:
Is the transaction log filling up?  That would suspend updates consistent with what you're talking about.

Greg
0
hkprsAuthor Commented:
yes, the transaction log is being filled up!!! Can you please be more eloborate abt updates being suspended?

Even though the query is still working on DB, the log file is filled up and log file has statements pertaining to next record.

Thanks in advance.

Regards,
0
gletiecqCommented:
OK, this is an entirely different problem than you presented at first.  It's not too hard to solve, though.

The easiest option is to simply increase the size of the transaction log.  A bigger log won't fill up so fast.  To do this you'll probably need to create an additional device, then extend the log segment to include that device.  Look at sp_addevice and sp_extendsegment documentation about how to do this if you're not familiar with the process, or use Sybase Central to perform this function.  Add log space to the database where the log is filling up, of course.  It's probably tempdb, but your query could

If you absolutely cannot devote additional space to the log, then you have to get creative.  One bit of this would be to dump the log in between queries.  Another, if you're doing a lot of deletions, is to use "truncate table" instead of a global "delete from".  Note that if this isn't a tempdb issue, you may have impacts on being able to recover from a media failure and your backups won't work if you do this unless you immediately perform a full backup.

I'd recommend increasing log space.

Greg.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
walterecookCommented:
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately 4 days.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

walterecook
EE Cleanup Volunteer
0
walterecookCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: gletiecq {http:#8200318}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.