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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.)
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.)
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Selecting a Recovery Model
http://msdn.microsoft.com/en-us/library/aa173531.aspx
screenshot:
http://filedb.experts-exchange.com/incoming/2008/04_w17/20010/sql-server-recovery-model.jpg
also:
http://support.microsoft.com/kb/873235
http://msdn.microsoft.com/en-us/library/aa173531.aspx
screenshot:
http://filedb.experts-exchange.com/incoming/2008/04_w17/20010/sql-server-recovery-model.jpg
also:
http://support.microsoft.com/kb/873235
ASKER
Okay, thanks everyone!
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.