Menu
Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.
To remove all data from an existing table, use the SQL TRUNCATE TABLE order. You can also use the DROP TABLE command to delete an entire table. But Truncate will remove the entire table structure from the database, and you will need to recreate the table if you want to store any data.
Note: The active transaction log file cannot be removed. Previously, we saw that once the primary log file becomes full, SQL Server uses the secondary log file. We need to make a secondary transaction log empty, so we can remove it.
To shrink a data or log file. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases and then right-click the database that you want to shrink. Point to Tasks, point to Shrink, and then select Files.
In management studio:
Properties, then Options.Tasks -> Shrink -> Files
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.
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.
backup log logname with truncate_only followed by a dbcc shrinkfile command
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With