How do I truncate a log file in SQL Server?

Shrinking a log file to a specified target size

SQL Dev servers typically use “Simple” for the Recovery Mode while SQL Prod servers use “Full.”

The SQL query below assumes that you are temporarily switching from Full Recovery Mode to Simple and then back to Full.

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.SQLCopy

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO  

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.