When we create a SQL Server database, it is created with a full recovery model. This grows the transaction log (the .ldf file) with every transaction we make on the database. Usually the size of the transaction file is many times more than the .mdf file.
If not maintained, the transaction log would eventually fill all the disk space that is available to the physical log files.
In a development/ testing environment, when the data is not important, the transaction log is not important. It is a good idea to maintain the database with a simple recovery model.
Run this command in the Query Analyser to change the database to a Simple Recovery model:
ALTER DATABASE [<DatabaseName>] SET RECOVERY SIMPLE
ALTER DATABASE [<DatabaseName>] SET RECOVERY SIMPLE
Run this command to reduce the size of the .ldf file:
DBCC SHRINKDATABASE ([<DatabaseName>], 20)
DBCC SHRINKDATABASE ([<DatabaseName>], 20)
When deploying the database to production, turn it back to the Full Recovery model:
ALTER DATABASE [<DatabaseName>] SET RECOVERY FULL
ALTER DATABASE [<DatabaseName>] SET RECOVERY FULL
Replace <DatabaseName> with the actual name of the database.
Read more:
MSDN Books Online: Transaction Log Management
MSDN Books Online: Transaction Log Management
