March 13, 2009 at 10:33 am
Hi
I have a problem. I need to delete some users(about 100) from my table Users, that have a certain prefix in their username(like 'KO%'). About 60 FK reference to this table, User_Id column. So I'm trying to delete those records, I don't receive any error, but the execution times out. I looked for orphaned records and I founded in some tables and deleted them, but still having the same problem.
Any opinions?
Thank you
March 13, 2009 at 11:03 am
Now I tried deleting "by hand", in SSMS, from the table a record an I receive again time out
The number of records in the table is 3000, and the table is not very big(2,5 Mb).
I don't see where is the pb, I'm too newbie...:)
March 13, 2009 at 11:14 am
1. Can you post a sample of your code here?
2. How long does it take before it times out?
3. Is it fair to assume that you are working with "default" settings (both on the client and server) with regards to connection properties?
March 13, 2009 at 11:30 am
I'm working from SSMS
The query is : DELETE FROM Users
WHERE Username like 'KO%'
That's all
And I've said that It doesn't allows me to delete only one record by hand.I don't know if that helps but I know that the problem is not the query...
March 13, 2009 at 12:54 pm
How long does it take before it times out?
Can you try this:
1. Start two sessions from SMSS (record both the spids)
2. In the first session execute the DELETE statement
3. Is the second session execute SP_WHO2 and see if there are any blocks
March 14, 2009 at 5:25 am
59 SUSPENDED saEUGEN .AntaresDELETE 547972917003/14 13:26:10Microsoft SQL Server Management Studio - Query59 0
That's what I get.I don't really know what this means....
March 14, 2009 at 5:37 am
Since it's not executing, can you post the estimated execution plan?
It sounds like you might be getting some blocking. Are there other processes hitting the table at the same time?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2009 at 8:34 am
Hi
How huge is the whole database? The user table maybe is only 2.5 MB but if your whole database is 500 TB ;). You wrote that there are 60 FK references, what type of FK did you use (CASCADE DELETE/UPDATE?)
If you use a cascade DELETE it is possible that your user deletion may become locked by it self.
Greets
Flo
March 14, 2009 at 9:31 am
Delete one row.
SET ROWCOUNT 1
DELETE FROM Users
WHERE Username like 'KO%'
SET ROWCOUNT 0
Or you can select from the data and find a specific row to delete.
Trace what happens, get the execution plan, and answer questions from above.
March 16, 2009 at 1:57 am
The whole database is 9 Gb
I have Cascade Update ok the Fk, but no Cascade Delete.
Unfortunetly I've "made a move" untill I read your replies.
I droped all the Fk , deleted the records and then recreate Fk. I had a dead line for that...
So ... I can't reply to Steve's reply...sorry.
Have I done a mistake with drop - create FK's?
Thanks for your replies
March 16, 2009 at 6:01 am
Only if you can't reapply them. It's fairly common practice for really large scale data moves to remove indexes and foreign keys at the beginning of the move and then reapply them at the end. You just have to have error handling in place to deal with issues if the data is messed up by the move. Testing, as usual, is the key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2009 at 6:44 am
Yes, I tested first, and I was lucky, because there were no problems
But if there were...I don't know if I could solve them
Anyway....I solve the problem . I'm not pleased, because I think it has to be a more..professional way, but It's ok for the moment. I will bother you again if I encounter that situation 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply