?
Solved

Query Running for Long time!!

Posted on 2003-03-20
11
Medium Priority
?
456 Views
Last Modified: 2008-02-01
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!!
0
Comment
Question by:hkprs
[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
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 8173850
Very critical and 50 points ?
Deadlock of asynchronous query ?
What SQL server ?
Post SQL code. Interface used. Connection settings.
0
 

Author Comment

by:hkprs
ID: 8175321
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
 
LVL 9

Expert Comment

by:ornicar
ID: 8176357
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:gletiecq
ID: 8192875
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
 

Author Comment

by:hkprs
ID: 8194048
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
 
LVL 4

Expert Comment

by:gletiecq
ID: 8195898
Is the transaction log filling up?  That would suspend updates consistent with what you're talking about.

Greg
0
 

Author Comment

by:hkprs
ID: 8200226
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
 
LVL 4

Accepted Solution

by:
gletiecq earned 200 total points
ID: 8200318
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
 
LVL 17

Expert Comment

by:walterecook
ID: 10348456
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
 
LVL 17

Expert Comment

by:walterecook
ID: 10392280
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

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