How to truncate transaction log file via sql statement

Sometimes you wish to truncate transaction log file via SQL statement. you don't want to create mess as you would have 24x7 (online) production database and recovery model would also be set to full.

Solution available before SQL SERVER 2005 was very simple. Just run below line and you have achieved your desired results.

backup log dbname with truncate_only

But in SQL SERVER 2005 and above, previous statement generates syntax error as it is no more supported in SQL SERVER 2005 and above. Then what is solution for SQL SERVER 2005 and above. Solution is very simple, a regular BACKUP LOG to a backup device is sufficient. That truncates the log. Use below statements in order to get your desired output

use master
alter database dbname set recovery simple
go
dbcc shrinkdatabase(dbname )
go
alter database dbname set recovery full

go

I hope it will solve your problem