[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-08
10
Medium Priority
?
529 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Janrow
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 22913706
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22913735
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22913742

 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 4

Expert Comment

by:joevi
ID: 22913759
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
 

Author Comment

by:Janrow
ID: 22913876
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1400 total points
ID: 22913891
>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
 
LVL 9

Assisted Solution

by:Ernariash
Ernariash earned 400 total points
ID: 22913953

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
 

Author Comment

by:Janrow
ID: 22914222
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22914243
0
 

Author Closing Comment

by:Janrow
ID: 31514722
Okay, thanks everyone!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question