I'm trying to delete rows from a database table using a stored procedure. Unfortunately, I the transaction log keeps filling up. I think I should be able to delete successfully in batches of 50,000. How do I do this within a stored procedure... The only thing I'm sure about is I use a while loop and a commit transaction.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Delete Query
- Thread starter bunmiA
- Start date
nigelrivett
IS-IT--Management
set rowcount 1000
while exists (select * from mytbl)
delete mytbl
set rowcount 0
Make sure the recovery model is set to simple. You may also need to include a manual truncate but it should be ok as it is.
set rowcount 1000
while exists (select * from mytbl)
begin
delete mytbl
checkpoint
end
set rowcount 0
or
set rowcount 1000
while exists (select * from mytbl)
begin
delete mytbl
backup log mydb with truncate_only
end
set rowcount 0
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
while exists (select * from mytbl)
delete mytbl
set rowcount 0
Make sure the recovery model is set to simple. You may also need to include a manual truncate but it should be ok as it is.
set rowcount 1000
while exists (select * from mytbl)
begin
delete mytbl
checkpoint
end
set rowcount 0
or
set rowcount 1000
while exists (select * from mytbl)
begin
delete mytbl
backup log mydb with truncate_only
end
set rowcount 0
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Status
Similar threads
- Locked
- Question