Help Deleting Large Number of Rows: Transaction Logs Fills up

[ErrolDC2]

I'm trying to delete a large number of rows from a table, but a bit after the operation begins, the Tlogs fills up and the operation stops. I've ran into this before and found a way to delete them using a Loop statement I think. I can't recall the information however. Can anyone help me out?

 

[DrSql]

use
[tt]
truncate
[/tt]

Dr.Sql
Good Luck.

 

[k108]

You should make sure the translog is set to autogrow (specify a reasonable percentage to avoid having to many small VLF's as the log grows in size), and make sure you have enough disk space to allow for the growth. As long as the log file is allowed to grow in size, and provided there is enough disk space, I don't see how it can fill up?

 

[DrSql]

If you are using TRUNCATE you dont have to worry about Translog becuase its not captured in transacation log. Check BOL for more details.


[tt]
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.

[/tt]

Dr.Sql
Good Luck.

 

[SQLBill]

Dr.SQL, that's for deleting ALL rows....the poster says "a large number of rows".

ErrolDC2,

Check out FAQ183-3141

-SQLBill

Posting advice: FAQ481-4875

 

[MDXer]

truncate only works if you want to delete ALL ROWS. The below isn't the most ideal way to do it but works if your TLog is an issue and the number of rows is large enough. you can set the 1000 values to whatever value best fits your scenario.

DECLARE @i bigint

Set @i = 1000

SET ROWCOUNT 1000


While @i = 1000
Begin

  DELETE FROM MyTable Where MyCol = MyVal
  Set @i = @@ROWCOUNT
End

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[k108]

Truncate works, but more importantly, he should make sure his translog file is configured correctly, otherwise he's just going to run into the same problem in the future on another long-running transaction.

 

[ErrolDC2]

Thanks MD! That is exactly what I was looking for!
I appreciate yours and everyone else's help.