Interesting. Thanks for the reply.

Sorry to jump in on this thread, but can I clarify if this is the recommended way to do this job just for...

Grant Fritchey (4/7/2015)

---

Hate to backtrack if it's been asked, but have you looked at the wait statistics on this? I'd be curious as to what it is waiting on. Maybe...

By now we've all heard of RBAR (Row By Agonizing Row) when it comes to looping and selects. However, there is also 'Insert By Agonizing Insert'; that is inserting for...

Thanks. I agree there are definitely some improvements that could be made with this design.

Unfortunately, I cannot implement the delete by hour, well not yet anyway. The machines run...

Thanks Grant.

I'm still not sure why the stats are being skewed. I've forced an update of the stats but the estimated is still massively more than actual. I agree, the...

Thanks for the help Grant.

Do you mean as follows: (Obviously changing the values dynamically for each pass in the real code).

DELETE DocumentResultDataItem

WHERE DocumentResultDataItem.ResultItemRecordId BETWEEN 225392327

...

Just to give an update on this and to ask for some further pointers:

Background - I'm trying to delete historic data from a number of tables which contain huge numbers...

Yes, you're correct; If the procedure fails, then running it again clears up whatever is left, so I am not worried by the process. I think the business owners are...

Thanks. I've written my procedure which deletes the data from the tables in batches. Initial testing shows it is much quicker and the log hardly grows at all,...

Thanks all, very interesting.

Unfortunately, a continual delete isn't possible as the data is loaded over the print run. Then reports are generated on that data, and it is used to...

Thanks.

Can I just check; BOL says about deleting data: "By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies".

If I break down the deletes...

BrainDonor (3/30/2015)

---

We remove multi-million rows after a variety of time-periods on several systems using partitioning.

It isn't an easy thing to create for an existing system that already contains a lot...

Thanks all for your help. I didn't want to run the job all the time as it can be quite intensive and users could be searching historical data whilst the...

Are you sure the instance is restarting? I get this issue, but it's just SSMS crashing and having to be restarted, not the instance. I am not sure what...