Lynn
rob.lobbe (9/15/2009)
--------------------------------------------------------------------------------
This works if you are the ONLY one using the database.
Running in a production system you can't just go about changing the recovery model.
as for a 'batch' delete
select 1
while @@rowcount > 0
begin
delete top () ....
end
if you are concerned about log growth have ANOTHER process manage it.
I'm not sure if you are talking about my code or not, but if you are, it will work in production
I read this article with fascination as I just finished this week deleting 6 years worth of records from a warehouse, with several tables needing 1.4 B records removed. Unfortunately our maintenance window is very small, and full. Deleteing records was a slow tedious process. I created procedures very similiar to what you had written, using batches of 10,000 as well as the rowlock hint. We were able to run this throughout the production day with minimal impact on the users. I checked frequently as obviously they took priority. Actually, without this we would still be working at it. That maintenance window I spoke of was only 4 hrs.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan