Autogrowth on Full Recovery Model SQL 2005
-
We recently changed three of our larger databases from Simple to Full recovery mode. Within a few days, the .ldf file had increased in size from 4G to almost 17G, which really put a hit on our disk size.
I may be interpreting this incorrectly, but since I had the autogrowth set to 10%, I thought that the file wouldn't increase by more than 10% each night. What caused this to expand in size so dramatically?
-
Auto-grow happens every time the log file runs out of room. So - if it's out of room at 12 noon it's not going to wait until after hours to grow. Without log space - all activity on DB stops.
It sounds like you should be doing log backups on a daily basis, possibly much more often than that. Since it's what would be used to allow you to replay activity past your latest database backup, your log backups should be "small and frequent" . If you're backing up the logs on the same schedule as your database, you may arguably be better off just switching your recovery mode back to simple (since you're losing a lot of the benefits of the FULL logging).
Backing up the log (not the database, the LOG) will allow it to truncate out committed activity, that is to say, free up the space so that it can be used again.
Also - in case you're doing this - It's not usually a good thing to shrink data or log files. Shrink tends to take a wrecking ball to the insides of your database. If the transaction log routinely need 4GB of data to track everything that it needed to do that day, then set the log size at 5 GB, and leave it alone (meaning - don't shrink it). If anything else - work the other way around: if you have a set size in mind you don't want the translog to go over - find out how often you need to back up the log in order to keep it there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I agree with Matt. Log backups are likely not being run and thus the log grows constantly.
You need to be sure you run full and log backups.
-
To add more to this when you set autogrowth to 10% it means that whenever the database is in need of space it grows 10% the size of now. suppose you have a log file with 1GB size then the growth would be 100MB when the log file size is incresed and this goes on until it hits the max disk space in the server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Extremely helpful stuff, Guys, I appreciate it!
To properly back up the log (Tasks-->Back Up-->Transaction Logs), I will need to re-set the recovery model to FULL, correct? And then just set the transaction log to back up more than just at the end of the night, due to the massive amount of data that is running through the DB?
Will that prevent the .ldf file from growing too large, too fast?
-
that's is in a nutshell...assuming that your activity is fairly spread out. Schedule your log backups more often (start at 4 times a day, and go up or down depending on the size you get).
You may find that one of those backups is much larger than the others, which would give you a time window to evaluate why the activity is so high during that time. For example - a nightly rebuild of every index will cause a LOT of activity on the T-logs, and is usually not necessary (not all indexes). You could then lower the frequency of that maintenance to something more appropriate, or make it more focused (rebuild some indexes nightly, most of the others weekly or monthly).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
What if your database grows to the maximum size in a single transaction so the plan can be better have a tlog backup every 1 hour in production hours atleast.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Hi,
I was unaware that shrinking the log causes such havok on the internals of your database :blink: I have our production database scheduled with log backups every 15 minutes and my logs still grow and sometimes I have to shrink them down. What else can be done if shrinking the log is not ideal?
I have alerts setup that go off for other databases that when a database log file reaches 85% full and kick off a job to backup the logs and shrink them down before they fill up. Is that not something I should be doing either?
Isabelle
Thanks!
Bea Isabelle -
Shrinking log files is sometimes required when they go over the size you want. I wouldn't do it a lot, but it sometimes happens. I would try to arrive at a log size that almost never requires a shrink.
Shrinking database files tends to leave things inside highly fragmented, to the point where you should rebuild all indexes and reorg if you ever need to shrink them.
More frequent backups is a very good strategy if you want to keep your T-log files small.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I will advice you to set the autogrowth in MB in place of in %. When you use % the server will have to calculate how much the 10% is going to be. 🙂
Success!
-
Hi friends,
One of my database datafile, autogrowth option set like 1MB growth and unrestricted, for log file it was 10% autogrowth. I think it is default settting.? Can I change datafile auto growth to 10% online or need to restart the database in order to take effect the changes. Please suggest. Am new to here and current DB size is around 3GB.
Thanks
Pratheep
-
n.pratheep (2/3/2009)
Hi friends,One of my database datafile, autogrowth option set like 1MB growth and unrestricted, for log file it was 10% autogrowth. I think it is default settting.? Can I change datafile auto growth to 10% online or need to restart the database in order to take effect the changes. Please suggest. Am new to here and current DB size is around 3GB.
Thanks
Pratheep
those are the default settings, why MS chose 1Mb is another question. These can be changed dynamically without a restart, for a 3Gb database file I would change to in the region of 100 - 200 MB growth. As for log depends on size of it currently and whether it grows.
If you have had this setting for a while and database has been growing in these samll chunks it would be worth your while scheduling some downtime to defragment the disk at the OS level using a tool such as Diskkeeper
---------------------------------------------------------------------
-
It depends on your business requirement. Consult your company or your manager before scheduling any backups as it might lead to some overhead on production systems.
If you dont need point-in-time recovery and your transaction log is growing then, its better to change the Recovery model from Full to Simple. If your business requires point-time-recovery then schedule Tlog backups and the freqeucy depending on how much data loss is acceptable?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply