Shrinking a database log file
Sep.25, 2008 in
SQL Server
Sometime, it looks impossible to shrink the Trucated Log file. Following code always shrinks the Trucated Log File to minimum size possible.
This script should allow you to shrink a DB log file.USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
September 25th, 2008 at 11:09 am
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.
Copy Code
USE AdventureWorks;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
– Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO