Babar Javaid (12/20/2011)
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)
IF this would be a continuous periodic job (say every month) i would recommend Batch approach in off peak hours, by this you can also manage you log.
Babar Javaid (12/20/2011)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.
rather deleting the data , i would say move the required data (latest) data into permanent tmp table (prefer new disk if you can or if you require) , rename existing table into some history table, and rename tmp into main table.in this way you can also put less overhead (log's space wise) on main table.and less data to operate.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)