Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mgn0528
Starting Member
7 Posts |
 Posted - 2004-10-13 : 10:08:43
|
| I'm having an issue with a SQL statement executing from a VB6 app.I'm executing a DELETE statement using ADO on a single table. Recently it has started getting hung on this statement and it never returns an error or times out. I can run numerous other statements against other tables in the database with no problem, so I'm thinking it's got something to do with the table itself. This same application is running at numerous other sites without any problems.What I've tried so far:I added some additional indexes based on the fields in my WHERE clause of the DELETE statement. When I first put the index in place it seemed to help, but after a couple of days it started hanging again. I've also rebuilt the existing indexes with the same result.I'm out of ideas as to where I should look or what needs to be tweaked to resolve this problem. Any ideas would be greatly appreciated. Thanks! |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-10-13 : 11:00:37
|
| Are there triggers/ cascading deletes also happenning on this table???I suspect you have somebody who has a lock on a record that you are trying to delete....and the delete is waiting 9forever0 until the lock is released.Search here for advice on debugging locking conditions. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-10-13 : 11:00:38
|
| How many rows in the table?How many rows are you trying to delete?What indexes exist? Remember, a delete needs to update all those indexes.Are you using IMAGE, TEXT data types?*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
mgn0528
Starting Member
7 Posts |
Posted - 2004-10-14 : 14:17:42
|
| AndrewMurphy:There are no triggers in the table. There is cascade deleting, but this table would be the end point (deleting a record in another table would delete records in this table, but no the other way).Locking is a possibility that I've considered, but I find it strange that it happens on the same statement every time when this table is accessed numerous times prior to this statement and after with no problems.Wanderer:There is a primary key index that includes all of the fields in my where clause. Normally this operation should delete about 6 to 10 rows of data. The table may contain 100s of 1000s of rows overall. There is a Text and an Image field in this table, though not directly referenced in the DELETE statement. What does that tell you?Thanks to you both for your replies! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-14 : 14:53:55
|
| Could you post the delete statement?Tara |
|
|
|
|
|
|
SQL Execute Locking Up