SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DELETE statement won't complete


DELETE statement won't complete

Author
Message
MissTippsInOz
MissTippsInOz
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1495 Visits: 597
This is an odd one. A DELETE statement with a single filter, deletes around 1.5 million records from a table (running a COUNT with NOLOCK verifies that the records are 'gone'), but then the statement never completes. In the sys.dm_tran_locks table, for this SPID, I can see one X lock on each resource partition (this is a 32 CPU server) except that there will always be a partition with one X lock and two further IX locks - it remains this way indefinitely!

I'm not sure how to investigate this in any greater depth. For info, at the moment the 1.5 million records are all that is in the table, so the filter is academic, but it won't remain this way.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Suresh B.
Suresh B.
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9252 Visits: 5330
There may be blocking issue. Please run the following query to confirm:
select status, blocking_session_id, wait_type, wait_resource
from sys.dm_exec_requests
where session_id = <spid>


Sudhakar Vallamsetty
Sudhakar Vallamsetty
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 77
Create a NON-Clustered index on the filtered column which you are issuing in DELETE statement and see if that fixes the dead locking issue to complete the statement.

It seems other SPID's are trying to process(select / update/ delete) the data in the table which you are trying to delete the data. You can run the profiler to capture deadlockgraph, if you want to see which statements are trying to access the data in that table.

*******Cool
Sudhakar
MissTippsInOz
MissTippsInOz
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1495 Visits: 597
Column is already indexed and no other blocking processes

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
yuvipoy
yuvipoy
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4543 Visits: 1451
Hey look at this Topic [Deleting large number of rows from a table]
http://www.sqlservercentral.com/articles/T-SQL/72606/

Thanks!
MissTippsInOz
MissTippsInOz
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1495 Visits: 597
yuvipoy (9/17/2012)
Hey look at this Topic [Deleting large number of rows from a table]
http://www.sqlservercentral.com/articles/T-SQL/72606/

Thanks!


I hope this doesn't come across as rude, but I work for a very large company, and look after many hundreds of databases - a 500GB DB is no big deal. Deleting 1.5million records from a table is not a large number of rows in my organisation and this same process will delete 10 times that number from other tables. Our tables are, generally(!) well indexed and optimised.

What I'm dealing with here is a very specific issue that has just come to light where this one statement is exhibiting odd behaviour and, seeing the odd locking behaviour, using lock partitioning I wondered if this could be pertinent to the issue.

Since originally posting I have been testing the impact of the indexes and also of the FKs that reference this table. I discovered that by disabling one of the FKs I was able to get this process to complete. With the FK in place I see an Update lock placed against the referencing table and the X/IX locks against a single partition in the table I'm deleting from and it goes no further. Just to clarify there are NO referencing records in the referencing table. But why would it get 'stuck' at this point?

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
MMartin1
MMartin1
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12259 Visits: 2051
Is it possible there is replication whereby both updates need to occurr at the same time (publisher / subscriber) ? If so there could be an issue on the other machine (perhaps a full log file).

----------------------------------------------------
How to post forum questions to get the best help
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)SSC Guru (167K reputation)

Group: General Forum Members
Points: 167911 Visits: 39506
MissTippsInOz (9/17/2012)
yuvipoy (9/17/2012)
Hey look at this Topic [Deleting large number of rows from a table]
http://www.sqlservercentral.com/articles/T-SQL/72606/

Thanks!


I hope this doesn't come across as rude, but I work for a very large company, and look after many hundreds of databases - a 500GB DB is no big deal. Deleting 1.5million records from a table is not a large number of rows in my organisation and this same process will delete 10 times that number from other tables. Our tables are, generally(!) well indexed and optimised.

What I'm dealing with here is a very specific issue that has just come to light where this one statement is exhibiting odd behaviour and, seeing the odd locking behaviour, using lock partitioning I wondered if this could be pertinent to the issue.

Since originally posting I have been testing the impact of the indexes and also of the FKs that reference this table. I discovered that by disabling one of the FKs I was able to get this process to complete. With the FK in place I see an Update lock placed against the referencing table and the X/IX locks against a single partition in the table I'm deleting from and it goes no further. Just to clarify there are NO referencing records in the referencing table. But why would it get 'stuck' at this point?


I think the reason for the suggestion was perhaps a single large transaction versus multiple smaller transactions. A sensible suggestion, even if not what you were looking for here.

Unfortunately, I don't have an answer to your question regarding the FK's. Not having access to the schema to see how things relate makes it a bit difficult. Plus, I haven't run into this issue either, so no first hand experience or knowledge.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search