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.
JanrowAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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.

Regards,

Patrick
0
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.

0
ErnariashCommented:

 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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

joeviCommented:
Janrow,

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.)
0
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?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I am NOT backing up the transaction log. Is that why it continues to grow so large?
yes.

>The reason for not backing it up is because it is so large.
you can do this:
BACKUP LOG yourdatabase WITH TRUNCATE_ONLY;
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErnariashCommented:

Janrow,  
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
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE 

Open in new window

0
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?
0
JanrowAuthor Commented:
Okay, thanks everyone!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.