Delete statement again

set rowcount 100000
declare @.a int
while 1=1
begin
begin tran del1
delete from dbo.extt_vpm_ldr_stg_2
select @.a = @.@.rowcount
print @.a
commit tran del1
if @.a < 100000 break;
end
Hi
TOTAL TABLE SIZE - 650 000 records
I am using the above statement to delete in chunks on 100000.
After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
should delete 100000 commit and delete the next 100000.
And the transaction log is suppose 100MB before the statement is
executed. It is increasing in steps of some 30MB for each delete to
upto some 250MB and then goes back to some 50MB.
What we need is the transaction log must increase for every delete and
drop and again increase and so on.
How can the above statement be modified to take care of this behaviour?
Please reply asap.
Thanks
HarishIs your databsae set to simple recovery mode? If it is set to full recovery
the transaction log will grow until you backup or truncate the log.
Note if you set to simple recovery mode you will not be able to use log
backups for log shipping or disaster recovery. Same thing if you truncate
the log (Backup Log <database name> with truncate_only).
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131552570.094908.5060@.z14g2000cwz.googlegroups.com...
> set rowcount 100000
> declare @.a int
> while 1=1
> begin
> begin tran del1
> delete from dbo.extt_vpm_ldr_stg_2
> select @.a = @.@.rowcount
> print @.a
> commit tran del1
> if @.a < 100000 break;
> end
>
> Hi
> TOTAL TABLE SIZE - 650 000 records
> I am using the above statement to delete in chunks on 100000.
> After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
> should delete 100000 commit and delete the next 100000.
> And the transaction log is suppose 100MB before the statement is
> executed. It is increasing in steps of some 30MB for each delete to
> upto some 250MB and then goes back to some 50MB.
> What we need is the transaction log must increase for every delete and
> drop and again increase and so on.
> How can the above statement be modified to take care of this behaviour?
> Please reply asap.
> Thanks
> Harish
>