What happens when I uncheck "automatically grow file" in SQL 2000's transaction log

My transaction log file is growing to 25 GB. That worries me. Now and again I'll detach the database, delete the LDF log file then reattach which then rebuilds the log file.

In the database properties there is an option to check or uncheck "automatically grow file". If I uncheck this option, will it stop the log file from growing, or beyond a certain limit, without any obvious problems to the functioning of the database? Or is there some real reason I need the transaction log? I'm willing to do without it unless it is critical for our purposes.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>I am NOT backing up the transaction log. Is that why it continues to grow so large?

>The reason for not backing it up is because it is so large.
you can do this:
that will clear the space internally.

then, run a full backup, and implement regular transaction log backups, and finally you can run the DBCC SHRINKFILE command for the log file.
Patrick MatthewsConnect With a Mentor Commented:
Hello Janrow,

If you are in full recovery mode, if you do not allow your log to grow, then you may find that some operations
fail because the log file runs out of room.


Guy Hengel [angelIII / a3]Billing EngineerCommented:
to clarify the issue:

your database is in full recovery mode, but you don't run any transaction log backups.
this will require the log file to grow, as sql server has to keep all the transactions (even the committed ones) in the log file.

run regular transaction log backups (besides your regular full backups), and the file will stop growing, and you can even shrink it to a reasonable size.

if really (really really) you do not need any transaction log backups (because the full backups are enough, ie you can afford to only perform full restores later instead of restores to a given point in time), then just change your recovery mode from full to simple. this will make that the log file space will be marked internally again for reuse once a transaction is committed/rolled back. again, after that change, you can shrink your transaction log file to a reasonable size.

note: dbcc shrinkfile is the command to shrink a database file.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!


 It all depends of your needs. If you do not need keep the transaction log you can set your recovery mode as simple, but usually it is not recommended  you wont have the added benefit is that it's possible to recover the database to a particular point in time.
but uncheck "automatically grow file" the when the transaction log becomes full, SQL Server Database Engine issues a error (9002).
You have different alternatives for responding to a full transaction log including: Backing up the log. Freeing disk space so that the log can automatically grow. Moving the log file to a disk drive with sufficient space. Increasing the size of a log file. Adding a log file on a different disk. Completing or killing a long-running transaction.
Here is what I do: I uncheck "automatically grow file" and back up the log to maintain the size under control: you can use the Maintenance Plan: Backup Database Task Backup Transaction Log.
Space in the transaction log is only reclaimed when a backup of the transaction log is made. When this occurs, all the changes stored in the transaction log are written to the backup and the space is freed up.

The important task is to identify why your log is growing so you may want to review what you're backing up and how as well as the environment (are you replicating?, does the database have a heavy transaction load?, etc.)
JanrowAuthor Commented:
The database load is not heavy, that is, not many records are added to it. However, it is accessed quite a bit. So,  heavy read access but very low write access.
I am NOT backing up the transaction log. Is that why it continues to grow so large? The reason for not backing it up is because it is so large.
Anyway, seems to me if I'm backing up the mdf database, and saving it elsehwere, I shouldn't need to worry that much about the log file. RIght?
But possibly more importantly, if I set the log file not to grow, would it then be best to do a one-time removal of the log file as mentioned above, rebuild it, then begin to back it up so that it does not grow so large? Am I understanding this correctly?
ErnariashConnect With a Mentor Commented:

Please see  angelIII as the recommended solution. But since you have mentioned you do not have many writes and do not need to have recover the database to a particular point in time. Just the full back ups on a different location the ease solution is to set your db to Simple recovery

-- set to Simple recovery

Open in new window

JanrowAuthor Commented:
Okay,  I'll give the majority to AngelIII. But how do I tell if my database is set to Simple recovery or not  and how to change it to Simple recovery if necessary?
JanrowAuthor Commented:
Okay, thanks everyone!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.