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

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.

Published Sunday, December 17, 2006 1:22 AM by Jon Galloway
Filed under: ,

Comments

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

Need sql server recovery software

Monday, December 10, 2007 8:39 AM by Megha

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

Nice article! I tried and it worked fine... thanks...

Friday, October 10, 2008 6:19 AM by Ratnakar Sinha

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

I found this file good. That helps me to  save my problem of shrinking database model on SQL Server 2005. Thank you.

Wednesday, February 4, 2009 12:28 PM by Hermann

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

Thanks for the post,

it was very helpful to me

i want to reduce some LDF files if my WSS 3.0

Friday, April 17, 2009 2:16 PM by Guy Vaknin

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

Excellent work. Very informative and nicely put together.

Look forward to reading more of your work in the future.

Wednesday, June 17, 2009 7:48 AM by Fun Anku

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

Just what I was looking for! Straight to the point and concise, top job. Cheers.

Thursday, February 25, 2010 11:35 AM by Ado

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

I do trust all the ideas you've introduced to your post. They're very convincing and will certainly work.

Still, the posts are too short for newbies.

May just you please prolong them a little from subsequent time?

Thanks for the post.

Friday, February 1, 2013 6:35 AM by Munson

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

In that respect, this item does work or at the quite least does not make untrue claims.

Wednesday, February 20, 2013 2:28 PM by Biddle

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

I have been exploring for a bit for any high-quality articles or blog

posts in this kind of area . Exploring in Yahoo I ultimately stumbled upon

this website. Studying this info So i'm happy to express that I have a very good uncanny feeling I came upon just what I needed. I most without a doubt will make sure to don?t omit this site and give it a glance regularly.

Wednesday, March 6, 2013 2:32 PM by Cline