4G table's delete uses 20G log

I've a table with a size of ~4G and 6million rows in SQL server 2005.
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealer
Do not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer