What is the command to truncate a SQL Server log file?

Question

I need to empty an LDF file before sending to a colleague. How do I force SQL Server to truncate the log?

Answer 1

In management studio:

• Right-click the database, choose properties, then options.
• Make sure "Recovery model" is set to "Simple", not "Full"
• Click Ok
• Right-click the database again, choose tasks -> shrink files
• Change file type to "log"
• Click ok.

Alternatively, the SQL to do it:

 ALTER DATABASE mydatabase SET RECOVERY SIMPLE
 DBCC SHRINKFILE (mydatabase_Log, 1)

Ref: http://msdn.microsoft.com/en-us/library/ms189493.aspx

Answer 2

backup log logname with truncate_only followed by a dbcc shrinkfile command

Answer 3

if I remember well... in query analyzer or equivalent:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

DBCC SHRINKFILE (  databasename_Log, 1)

Answer 4

+1 to ila's answer.

(I've run out of votes today)