funooni (8/8/2014)

---

Dears ,

I have to delete data from HUGETABLE which has around 700 million rows prior to 1st May 2014. This table has no Index, however, it has an ID column.

Now in order to avoid the log growth, I wrote the below code. Assuming that it will delete 1 million rows and at any time I cancel it; it will rollback only the last transaction. But on the other hand when I cancel it; it starts rolling back all the rows back into the table.

Any idea, how can I achieve it without running out of LOG File Space ?

Next thing I have in my mind is to schedule a job to delete around a milllion rows every 10 minutes and truncate the log at the end of the job.

Appreciate experts feedback.

begin try drop table #counter end try begin catch end catch

create table #counter (id int )

insert into #counter SELECT top 1 ID From HUGETABLE with (nolock) where DateTime < '2014-05-01'

while (select COUNT (1) from #counter) > 0

begin

begin transaction deletemol

truncate table #counter

insert into #counter SELECT top 10000000 ID From HUGETABLE with (nolock) where DateTime < '2014-05-01'

if exists (select top 1 1 from #counter )

begin

delete from HUGETABLE where ID in (select ID from #counter)

end

truncate table #counter

insert into #counter

select top 1 ID From HUGETABLE with (nolock) where AuditDateTime < '2014-05-01'

select GETDATE ()

commit transaction deletemol

DBCC SHRINKFILE (N'DB_log' , 0, TRUNCATEONLY)

END