Zayko in the Net

Personal blog of Vitaly Zayko

NAVIGATION - SEARCH

How to shrink LOG files in Microsoft SQL Server 2005 and 2008

You know that LOG files of actively used SQL Server databases could become huge, even bigger than data (MDF) file.
In SQL2005 you can use this script to shrink LOG file size:

USE <database name>
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log file name>, <preferred size>)

But this is no longer working in SQL2008. So do this:

USE <database name>
ALTER DATABASE <database name> SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log file name>, <preferred size>)
ALTER DATABASE <database name> SET RECOVERY FULL

Good luck!