Delete Data without impacting Tempdb

This is a SQL 2005 production server.

I have to delete around 51 million rows from a table which has 149 million rows.

Can't use truncate option as the other rows in the table are still needed.

How can I delete the rows without filling up the tempdb ?

If the tempdb fills up I can't bounce the server.Set your recovery mode to Simple so you don't log all the deletes. Consider deleting your data in smaller batches.|||I've been doing this exact thing for the past week, and did exactly that.

Just remember to defrag/rebuild indexes following this.|||tempdb? maybe you need to check whether you are trying to support snapshot isolation. In that case, the deleted rows will end up in tempdb prior to the commit.|||The recovery mode is "simple". Its still putting all the transaction in the log file.

The Tempdb detail was an error from my side.|||in that case, simply break the delete down into smaller chunks. The entire delete will always be logged. So instead of one big delete, make it 10 smaller deletes.|||thanks for all the responses.
The only solution seems to delete in small batches and then truncate the log.|||You should not need to truncate the log in simple recovery mode.|||Somehow it seems that some of the transaction log keeps getting held after large transactions. I find running checkpoint in the affected database brings things back in line. Checkpoint is also a side effect of truncating the log.|||Mcrowley,
That is exactly the issue I have. I have to truncate the log after doing the large delete inspite of recovery mode simple.|||After your done truncate with no_log and dbcc shrinkfile it back down.|||Bear in mind if you are performing this delete operation during normal day time it will have stress on tempdb too in addition to user database's transaction log.

So better to perform this operation during less traffic hours and also perform CHECKPOINT after couple of thousand rows to keep up the log size.

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.

> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.

> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
David

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> does it possible to truncate a specific partition on a partitionned
>> table?
>> I have a large table where each partition contains 50 million of rows, I
>> have to truncate 1 partition (which is 1 year) and then refill it.
>> its a data warehouse, so there is no concurrent usage, its during my
>> loading process.
>> does the delete statement is good enough?
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
--
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.
> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.
> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
Davidsql