Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query Running for Long time!!

Posted on 2003-03-20
11
Medium Priority
?
465 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
  • 3
  • 3
  • 2
  • +2
10 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

577 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