How to truncate log in SQL Server 2008?

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.

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

USE DATABASE;GOSELECT file_id, nameFROM sys.database_files;GODBCC SHRINKFILE (1, TRUNCATEONLY);

Below is some code I use in a maintenance place to truncate all transaction logs, which works well
====================================================================SET NOCOUNT ON CREATE
TABLE #TransactionLogFiles (DBName VARCHAR(150), LogFileName
VARCHAR(150) )DECLARE
DBList CURSOR FOR SELECT
name FROM
master..sysdatabasesWHERE
NAME NOT IN ('master','tempdb','model','msdb','distribution')DECLARE
@DB VARCHAR(100)DECLARE @SQL VARCHAR(8000) OPEN
DBList FETCH
NEXT FROM DBList INTO @DBWHILE
@@FETCH_STATUS <> -1 BEGINSET @SQL = 'USE ' + @DB + ' INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''
+ @DB + ''', Name FROM sysfiles WHERE FileID=2'EXEC(@SQL) FETCH NEXT FROM DBList INTO @DB END
DEALLOCATE DBList DECLARE
TranLogList CURSOR FOR SELECT
DBName, LogFileNameFROM #TransactionLogFiles DECLARE @LogFile VARCHAR(100) OPEN
TranLogList FETCH
NEXT FROM TranLogList INTO @DB, @LogFileWHILE
@@FETCH_STATUS <> -1 BEGIN --PRINT @DB +',' + @LogFileSELECT
@SQL = 'EXEC sp_dbOption ' + @DB + ', ''trunc. log on chkpt.'', ''True'''EXEC
(@SQL) SELECT
@SQL = 'USE ' + @DB + ' DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'EXEC
(@SQL) SELECT
@SQL = 'EXEC sp_dbOption ' + @DB + ', ''trunc. log on chkpt.'', ''False'''EXEC(@SQL) FETCH NEXT FROM TranLogList INTO @DB, @LogFile END
DEALLOCATE
TranLogListDROP
TABLE #TransactionLogFiles

Perfect for what we needed. However we use a bookkeeping package that numbers the administrations as DB's. For that I have cleaned up the formatting and added appropriate brackets:

SET NOCOUNT ON

CREATE TABLE #TransactionLogFiles (
DBName VARCHAR(150),
LogFileName VARCHAR(150)
)

DECLARE DBList CURSOR FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
AND status & 512 = 0

DECLARE @DB VARCHAR(100)
DECLARE @SQL VARCHAR(8000)

OPEN DBList
FETCH NEXT FROM DBList INTO @DB WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'USE [' + @DB + '] INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''+ @DB + ''', RTRIM(Name) FROM sysfiles WHERE FileID=2'
EXEC(@SQL)
FETCH NEXT FROM DBList INTO @DB
END

DEALLOCATE DBList

DECLARE TranLogList CURSOR FOR
SELECT DBName, LogFileName FROM #TransactionLogFiles
DECLARE @LogFile VARCHAR(100)

OPEN TranLogList
FETCH NEXT FROM TranLogList INTO @DB, @LogFile WHILE @@FETCH_STATUS <> -1
BEGIN
--PRINT @DB +',' + @LogFile
SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''True'''
EXEC (@SQL)
SELECT @SQL = 'USE [' + @DB + '] DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'
EXEC (@SQL)
SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''False'''
EXEC(@SQL)
FETCH NEXT FROM TranLogList INTO @DB, @LogFile
END

DEALLOCATE TranLogList
DROP TABLE #TransactionLogFiles