I want to replace the coding Backup Log with Truncate_Only in a sql script.  If I understand correctly, I need to do the following:

1. alter database MyDatabase set recovery simple
2. checkpoint

Does setting the recovery model cause a checkpoint or I need to issue checkpoint statement manually?

After the database is set to simple recovery model, what is the difference between executing checkpoint statement and backup log with truncate_only in terms of SQL Server 2005?

Thanks.

Backup log with trucate_only is no longer supported in SQL 2008. If your database is in bulk-logged or full recovery model then schedule T-Log backup on regular interval and it will keep your t-log is shape. 

We can not issue backup log command in simple recovery model. 

What exactly you wants to achieve here? Do you have any issue with disk size!! 

Hi ghemant,

My goal is just to free up log space.  The backup log with truncate_only does exactly that.

I'm doing a mass data manipulation of all tables in a database.  A full backup will be done before this task.  My understanding is that checkpoint will cause log truncation in simple recovery model.  I want to free up log space for reuse after data manipulating of each table so that the log file size won't grow too much.  I don't want to depend on the automatic checkpoint to occur so I want to issue checkpoint statement to force checkpoint to occur. 

Does log truncation occur for executing checkpoint statement manually? This old MS article for SQL Server 6.5 and older has this statement: Note that the log will not be truncated when issuing a manual checkpoint command in a database with "truncate log on checkpoint" on.  However, I don't think this statement is valid for SQL Server 2000, SQL Server 2005, and SQL Server 2008. I guess it is valid for SQL Server 7.

Is there any setting that will prevent log truncation on checkpoint in SQL Server 2008?

 It is best to do regular log backup as suggested by ghemant 

in sql 2008 , The transaction log is automatically truncated when the database is using the simple recovery model.
review the notes for Discontinued Database Engine Functionality in SQL Server 2008 in:
http://msdn.microsoft.com/en-us/library/ms144262.aspx

so, if you are in full recovery model , you can run:

alter database <mydb> set recovery simple
go

checkpoint
go

alter database <mydb> set recovery full
go

backup database pubs to disk = 'c:\mydb.bak' with init
go

dbcc shrinkfile (N'mydb_log' , 1)
go


 

Check the following blog too...

http://madhuottapalam.blogspot.com/2008/05/faq-how-to-truncate-and-shrink.html