Ed Wagner (10/23/2016)

---

Rechana Rajan (10/23/2016)

---

Eirikur Eiriksson (10/23/2016)

---

Ed Wagner (10/23/2016)

---

Eirikur Eiriksson (10/23/2016)

---

Rechana Rajan (10/23/2016)

---

We are creating a data archive plan for one oof our big table which have more than 8 million records in jusst 8 months . Planning to keep only 6 months record in this and to move rest to another table in another DB

The advantage in moving to another DB I see is that we ccan reduce the size of existing DB and also the backup size of existing DB will reduce. Do we have a better plan?

My concerns are both insert into new table and delete from existing table is logged operation which will cause log to grow. Have to do a shrink file to reclaim the space, How can I avoid or better do this?

Thanks

If you run the archiving process frequently enough then the sizes are manageable, 1 million rows per month are only 1370 rows an hour on average, suggest you try to set it up to run hourly.

😎

Why would you try to reclaim space from the log file? If you reclaim the space, it'll just need the space again when the archiving process runs again next time. If you shrink the log again, it'll be needed again. This will continue in perpetuity and will only serve to slow things down while the log file grows to where it needs to be.

Instead, I'd suggest letting the log file grow to what it needs and leave it alone. When you run your log backups, the space in the log will be able to be reused, so the log file won't have to continually grow.

Don't think the OP is referring to the log but the data file, agreed that reclaiming the log space isn't too clever.

😎

yes, meant the data file

My mistake. I thought you were asking about both the data file and the log file separately.