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.

default tempdb size

Hello All,
SQL 2K W/SP3
Is it possible to change default tempdb size to something else other than
2MB so everytime server restarts, tempdb does nto go back to 2 MB?
Thanks,
BivaYep. You can also change location for the db files. Info is stored in
master..sysaltfiles, and you change it using ALTER DATABASE (see Books
Online for details).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...
> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>|||Biva,
ALTER DATABASE should work just fine.
Russell Fields
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...
> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>

default tempdb size

Hello All,
SQL 2K W/SP3
Is it possible to change default tempdb size to something else other than
2MB so everytime server restarts, tempdb does nto go back to 2 MB?
Thanks,
BivaYep. You can also change location for the db files. Info is stored in
master..sysaltfiles, and you change it using ALTER DATABASE (see Books
Online for details).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...

quote:

---

> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>

---

|||Biva,
ALTER DATABASE should work just fine.
Russell Fields
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...

quote:

---

> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>

---

|||Hey Guys,
Thanks a bunch for the info ...
sqlgirl
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!