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

User · Answer

For SQL Server 2008  the command is   ALTER DATABASE ExampleDB SET RECOVERY SIMPLE DBCC SHRINKFILE  ExampleDB log   0  TRUNCATEONLY  ALTER DATABASE ExampleDB SET RECOVERY FULL   This reduced my 14GB log file down to 1MB

User · Answer

For SQL 2008 you can backup log to nul device   BACKUP LOG  databaseName  TO DISK    nul   WITH STATS   10   And then use DBCC SHRINKFILE to truncate the log file

User · Answer

Another option altogether is to detach the database via Management Studio  Then simply delete the log file  or rename it and delete later    Back in Management Studio attach the database again  In the attach window remove the log file from list of files   The DB attaches and creates a new empty log file  After you check everything is all right  you can delete the renamed log file   You probably ought not use this for production databases

User · Answer

backup log logname with truncate only followed by a dbcc shrinkfile command

User · Answer

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 · Answer

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 · Answer

if I remember well    in query analyzer or equivalent   BACKUP LOG  databasename  WITH TRUNCATE ONLY  DBCC SHRINKFILE    databasename Log  1

[sql-server] What is the command to truncate a SQL Server log file?

Examples related to sql-server

Examples related to truncate

Examples related to logging