SQL Server recovery model selection (or, what's this 3GB LDF file?!)


SQL Server recovery models can be a bit tricky. By default, SQL Server databases are set to Full Recovery mode, which means they maintain sufficient logs to restore to any specific point in time. These logs can grow to several GB for a small, lightly used database over time. For a development workstation, especially if your database is under version control, I like to change the recovery mode to Simple (most cases) or Bulk-Logged at most. Then you can shrink the database, which will clear out the log file in Simple mode - you can do this in SQL Server Management Studio by right clicking on the database, selecting Shrink, then Database. Keep in mind that shrinking databases can be a little more complex that I've pointed out here, but that's not normally important on a developer workstation.

Now, that solves the problem for this one database, but it will crop again the next time you create a database. A good way to deal with this once and for all is to change the recovery mode of your Model database to Simple or Bulk Logged, since that setting will apply to all future databases you create. Just make sure to update the recovery mode when you database is deployed to a production environment.

This is just a simplistic solution for SQL Server running on a developer machine - see some more in depth information on the MS Support site.


Comments have been disabled for this content.