What is the command to truncate a SQL Server log file?
I need to empty an LDF file before sending to a colleague. How do I force SQL Server to truncate the log?
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
, thenOptions
. - Make sure "Recovery model" is set to "Simple", not "Full"
- Click OK
- Right-click the database, choose
- 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
if I remember well... in query analyzer or equivalent:
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( databasename_Log, 1)
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.