Tell me more ×
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.
up vote 0 down vote favorite
1

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 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

up vote 3 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. For additional reducing, which I don't think is necessary, you can:

2 Set Auto Shrink to True

enter image description here

3 Turn auto-grow off

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? – Floran Jan 14 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 at 15:00
2  
No, please do not ever set auto shrink to true for any file in SQL Server. – Aaron Bertrand Jan 15 at 22:44
up vote -1 down vote

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.