Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?

My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again. How can I permanently limit the filesize or perhaps even disable it entirely (since I don't think I'll ever use it).

share|improve this question

marked as duplicate by Martin Smith, Mark Storey-Smith, Thomas Stringer, Aaron Bertrand, Jon Seigel Jan 12 '13 at 19:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers 2

up vote 5 down vote accepted

You cannot disable/remove the LDF file. It has to exist, as it stores database transactions. What you can do is:

  1. Set the database recovery model to Simple

This should keep your LDF file from growing, and is set via the Options page of the Database Properties dialog.

For additional reducing, which I don't think is necessary, you can:

2 Set Auto Shrink to True

This is also done from the Options page of the Database Properties dialog:

enter image description here

3 Turn auto-grow off

This is done from the Files page of the Database Properties dialog:

enter image description here

Be aware that when a database is in the Simple recovery model, you will not be able to recover to a point in time

Or

  1. Set the database recovery model to Full (I guess it's already set this way)
  2. Create a full database backup
  3. Start creating transaction log backups regularly. It's best to schedule a SQL Server job to do this

The latter scenario provides better disaster recovery as it enables recovery to a point in time It's up to you to decide how much data you can afford to lose

share|improve this answer
    
What if I set the recovery model to Full (wasnt this way now), and set the Auto Shrink to true? Daily backups of the DB are already in place. Wouldn't that result in a non-growing ldf log without risk of losing more than 1 day of data? –  Flo Jan 14 '13 at 13:12
1  
@Florian Yes, daily database backups will provide losing maximum 24 hours of data. For the scenario you described, you don't need the Full recovery model and auto-shrinking. Having a database in Simple recovery and daily full database backups should be enough. Full recovery model is used only for a point in time recovery, and auto-shrink might cause performance problems: support.microsoft.com/kb/315512 –  Carol Baker West Jan 14 '13 at 15:00
3  
No, please do not ever set auto shrink to true for any file in SQL Server. –  Aaron Bertrand Jan 15 '13 at 22:44
    
I was about to post this link here before seeing that @AaronBertrand had commented this itself. You could check Paul Randal's article on the topic as well. –  Jaime Sep 30 '13 at 11:10

You can take a look at the answers I received and posted in this thread: SQL Server virtual log size

I'm not sure if it's possible to disable the log completely, but the way I solved it has kept the logs from growing out of control.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.