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

sql-server truncate logging
Aidan Ryan picture Aidan Ryan · Sep 2, 2008 · Viewed 500k times · Source

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

Answer

Blorgbeard is out picture Blorgbeard is out · Sep 2, 2008

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

Related questions

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)? I know that I can either Use a DELETE without a …

Read More
How can I truncate a datetime in SQL Server?

What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008? For example: declare @SomeDate datetime = '2009-05-28 16:30:22' select trunc_date(@SomeDate) ----------------------- 2009-05-28 00:00:00.000

Read More
Pros & Cons of TRUNCATE vs DELETE FROM

Could someone give me a quick overview of the pros and cons of using the following two statements: TRUNCATE TABLE dbo.MyTable vs DELETE FROM dbo.MyTable It seems like they both do the same thing when all is said …

Read More