Hi,

I have a scheduled job that does an archival job. Archive process
deletes records by family i.e., say the application is based on travel
trips, "trip"s are archived one by one. When a "trip" is archived,
records related to that "trip" in various related tables are deleted in
proper hierarchy. Deletion itself takes long time (around 3 minutes per
"trip") on top of that it makes any other operation insert, update,
select to wait even though the data that's getting deleted is not at
all related to them.

I do deletes based on the primary key only ... some thing like

DELETE FROM TABLE1
WHERE TABLE1.PRIMARYKEYCOLUMN in (SELECT tempids FROM temptable)

Most of my selects have nolock. But still when this archival process
runs application is not usable by anyone else. Let me know if I'm
missing something. and if there is any solution, workaround to this
problem.

thanks for your help.

Regards,
Raja