CheckPoint Vs DBCC Shrinkfile

Question

i have million of records insertion and procession in a table. DB is in Full recovery Mode. when Any insert command and group by etc.. executed it filled off ldf file(of size 1mb) to (120 GB).

Shld i use Checkpoint or Alter database set recovery simple and then shrinkfile etc.

i think as i am inserting data

Answer 1

Checkpoint will not help you in reducing transaction log size with Full recovery model. I see two option here:

• truncate transaction log (BACKUP DATABASE [YourDBName] WITH NO_LOG), though this will not work with SQL 2008+ as this option is discontinued (read more details here)
• switch to Simple recovery model (which is recommended way of freeing space in transaction log (see 'Transaction Log Truncation' section))

Both of the above option deal with freeing space. After using any of them you will have to shrink log file anyway.

Answer 2

Backup your transaction log on a very regular basis (e.g. every 5 minutes).

This will allow the transaction log to be reused and it won't grow as large.