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