TheSQLGuru (6/25/2013)
I would have nonclustered indexes on the requisite fields required to find the aged records (some date field on all parent tables I would presume). Child tables would need to have indexes on FKs to parents. Then I would do a batching delete on each set of table(s), doing maybe a few thousand rows at a time with a rowlock hint. All within a transaction per table/table set. Iterate each set until rowcount < batch number. Put a waitfor delay in between each batch to give server some breathing room. Maybe watch for tlog size and perhaps fire off a tlog backup every so often based on that.I have done this type of thing at many clients over the years.
Thanks Kevin for the reply.
1. Index-wise I'm good.
2. I do not have batching right now, I can add that. Do you recommend doing round trips from the .net app the database for each batch or just call a stored proc with very long time out and let it handle everything.
3. I see why you are proposing rowlock hint, but is it ok to have few thousand rowlocks over pagelocks or index locks?