February 2, 2008 at 10:04 am
Hi,
I've run into a strange problem. We have SQL Server 2000, sp3. I'm trying to delete data from about 50 tables. I need to delete the data for about 240K ids.
I ran the deletes for each table with about 2K ids at a time. So, I have 2K ids in a staging table; for each main table I go against this staging table and delete the corresponding rows.
I started with 2K, increased gradually and went up to 5K, 10K, 25K, 35K and 51K. The deletes went easily. I finally had 67K rows I needed to delete. I deleted from about 43 tables. On the 44th table (has 676,877 rows), it wouldn't finish - just hanging. It took 19 seconds to delete 51K rows in the previous round, but now didn't finish in 30 minutes. We tried updating statistics and re-indexing the table. We truncated the transaction log before running this. We even tried to delete with 1K rows, still hangs. Then I tried deleting just one row with the id specified - again it hangs. The show plan command on the delete statement also hangs. However, a select statement will work very quickly. There are no other users on the system.
Can anyone please help?
Thanks,
VP
February 2, 2008 at 11:20 am
Can you do a 'TRUNCATE TABLE'?
February 2, 2008 at 11:25 am
Did u find any locks in the table. Then is the table having any triggers in that. if so disable them and then try running against the table.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 4, 2008 at 6:49 am
All,
I cannot do a truncate on the table.
There are three triggers - 1 for update and 2 for insert and updates. So, they should not be affected by this delete.
There are no other locks on the table - this was the only query to run during this time.
I really appreciate any help you can give me.
Thanks,
VP
February 4, 2008 at 7:03 am
check if the table is heavily indexed. if so try updating stats. then do the delete. i think the table would be heavily frgmented check for that too. it shld help.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 4, 2008 at 7:33 am
Sugesh,
We did an update statistics and a re-indexing on that table. Didn't help.
Thanks,
VP
February 4, 2008 at 7:49 am
could you post ur delete stmt here. if you don't want to make it public then you can write a private message too.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 4, 2008 at 7:54 am
Sugesh,
This is the statement:
Delete a from dbo.SubComponent a join ArchivedID b on a.CompID = b.CompID
I've tried the following too with no success:
Delete from dbo.SubComponent
where CompID in (select CompID from ArchivedID)
Delete from dbo.SubComponent
where CompID in (1111, 2222, ....)
Delete a from dbo.SubComponent a, ArchivedID b where a.CompID = b.CompID
Thanks,
VP
February 4, 2008 at 5:05 pm
When you do a SELECT statement, do you get anything?
Worst comes to worst, create a table exactly liked the table you want to delete and call it _bak. Don't worry about the trigger. Insert all the data from the table to table_bak.
Do the delete statement using table_bak and see if it works.
If it does, save the code of the trigger, then drop the table.
Rename the table_bak to table.
Re-Create the trigger.
my 2 cents.
Good Luck.
February 5, 2008 at 4:48 am
When I do a select, I get back rows - instant response.
I was able to delete the rows last night after hours. I used the original statement. I first did 1K, then 5K, 15K, 25K and finally 25K.
That is what confuses me. Why could I not delete them originally? Nothing changed about the data. What could have prevented me from deleting them 2 days ago? I had done a update statistics and re-indexing. The transaction logs were truncated between the deletes. What else could have caused this hanging?
Any ideas?
Thanks,
VP
February 5, 2008 at 5:00 am
Perhaps you have some DB corruption? I've seen this sort of thing happen with a corrupted index. The select happens to not use the problem index so you don't see the problem but the delete, by its very nature, needs to alter all of the indices.
Could you drop the indices and try the delete then (although it may run VERY slowly)?
February 5, 2008 at 5:20 am
Wouldn't re-indexing take care of the corruption if present? There is only one index on the main table - a primary key on the CompID and SubCompID columns.
Copying the data into another table and deleting from that would have been my next thing to try and the final try would have been dropping the index and deleting.
But I didn't need to do any of that last night. It got deleted without any problems. That is what is puzzling. I will need to do this same deletes again periodically and I don't want to have to split it over many days just because I cannot figure out what is happening.
Thanks,
VP
February 5, 2008 at 8:54 am
This really sounds like a blocking issue. I read through all the threads, but don't recall you saying if you checked for locks (on the process), not just the one table.
February 6, 2008 at 1:31 pm
Hi,
As suggested in earlier posts drop/disable the index and try to delete the data. Instead of deleting all the records at one shot try deleting in small chunks of it. But before you do all these things make sure that no other process is using the table and placing the locks.
Thanks -- Vj
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply