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

ajryan_1042 0
Viewed 530083

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

truncate logging sql-server
7 Answers

In management studio:

  • Don't do this on a live environment, but to ensure you shrink your dev db as much as you can:
    • 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

user_3690

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

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

DBCC SHRINKFILE (  databasename_Log, 1)
ila_11780

backup log logname with truncate_only followed by a dbcc shrinkfile command

user_7400

Since the answer for me was buried in the comments. For SQL Server 2012 and beyond, you can use the following:

BACKUP LOG Database TO DISK='NUL:'
DBCC SHRINKFILE (Database_Log, 1)
user_312780
Related