March 12, 2009 at 12:51 pm
Not sure why buy my on one set of databases the transactions logs are consistently growing and I have to manually shrink them about once a week.
The 3 databases in question are set to full recovery mode, a daily full backup is run each evening and hourly transaction log backups. The jobs were created using the maintenance plan wizard.
What am I missing? I thoughts having the full backup and regular Transaction-Log backups would keep the size under control but they just keep growing and do not "reset".
March 12, 2009 at 2:33 pm
How often are you doing you T-Log backup? The transaction log will keep growing and the transactions will not get truncated until you do backup.
So lets say ..
8AM You TLog file is 50MB..
9AM now its 60MB
9:30AM You do T-Log backup (All completed transactions are backed up and are ready to be over written).
10:00AM now its 70MB (Why did it it increase in size when there was a backup at 9:30? Someone did a Batch Import/Bulk insert? That caused the T-Log to use up all free space + request additional space).
* Backing up T-Log does not shrink physical file size.
* Backup up T-Log will only backup completed transactions not everything in log.
* Backup up T-Log will truncate the records that were completed.
* If your T-log is growing too fast check auto growth settings make sure it is not set to default of 10% (if you reach in to GB size TLog).
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 6:45 am
The transaction log backups run every hour and a full backup is run once a day.
Thank you for the information on what the transaction log backup does and doesn't do. This will be helpful as I look at the issue.
Why would the logs not reduce in size on their own but do when I manually run the shrink - file - log file process?
Is there a "best practice" or "rule of thumb" on setting the auto growth size? If 10% is too much should I try 5% or less?
March 13, 2009 at 9:20 am
glemke (3/13/2009)
Why would the logs not reduce in size on their own but do when I manually run the shrink - file - log file process?Is there a "best practice" or "rule of thumb" on setting the auto growth size? If 10% is too much should I try 5% or less?
Turncate and Shrink file are two different ideas. There is a functionality to auto shrink files but it is a very bad idea because it can have significant performace impact on your computer with continues shrinking/expanding the file.
Truncate means the records that have been hardened, that is the modified pages have been written to the actual database, are marked for overwrite. That is lets say our log has following records
Record 1
Record 2
Record 3
Record 4
Record 5
Empty Space
Empty Space
Record 6
Record 7
Now we do a backup, but only record 1-5 are done .. so..
Empty Space
Empty Space
Empty Space
Empty Space
Empty Space
Empty Space
Empty Space
Record 6
Record 7
So all that empty space now avalaible to be used by your database; but not release back to the operating system. When you do a manual shrink file you are asking the SQL Server to look at at "Empty Space" in the T-Log and release it back to OS. YOu should be only shrinking the T-Log file if you have HDD Space issues; if it keeps growing it means it needs to be that size to sufficently serve your database.
Rule of thump for growth rate don't use defaults on large databases. Because lets say your T-Log is 1GB, with default 10% Expansion
1GB -> Expand 10% -> 1.1GB? Doesn't sound bad?
But now lets say your T-Log is 25GB with 10% Expansion?
25GB -> Expand 10% -> 27.5GB?
So if you keep expanding on this example it is easy to see the growth can be expendintial which will eat up your hdd alot faster as the database gets bigger. This setting should be considered carefully for both MDF and LDF. You should choose the growth value by MB and base it on your experince on how much it grows. You want it large enough so it is not having to expand continously to keep up with the demand.
On a 60GB dB, with Log file of 18GB; I hae set the Log Expansion to 512MB. Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 11:32 am
Excellent explanation!
Thank you.
March 28, 2009 at 4:41 pm
Mohit (3/13/2009)
glemke (3/13/2009)
Why would the logs not reduce in size on their own but do when I manually run the shrink - file - log file process?Is there a "best practice" or "rule of thumb" on setting the auto growth size? If 10% is too much should I try 5% or less?
Turncate and Shrink file are two different ideas. There is a functionality to auto shrink files but it is a very bad idea because it can have significant performace impact on your computer with continues shrinking/expanding the file.
Truncate means the records that have been hardened, that is the modified pages have been written to the actual database, are marked for overwrite. That is lets say our log has following records
Record 1
Record 2
Record 3
Record 4
Record 5
Empty Space
Empty Space
Record 6
Record 7
Now we do a backup, but only record 1-5 are done .. so..
Empty Space
Empty Space
Empty Space
Empty Space
Empty Space
Empty Space
Empty Space
Record 6
Record 7
So all that empty space now avalaible to be used by your database; but not release back to the operating system. When you do a manual shrink file you are asking the SQL Server to look at at "Empty Space" in the T-Log and release it back to OS. YOu should be only shrinking the T-Log file if you have HDD Space issues; if it keeps growing it means it needs to be that size to sufficently serve your database.
Rule of thump for growth rate don't use defaults on large databases. Because lets say your T-Log is 1GB, with default 10% Expansion
1GB -> Expand 10% -> 1.1GB? Doesn't sound bad?
But now lets say your T-Log is 25GB with 10% Expansion?
25GB -> Expand 10% -> 27.5GB?
So if you keep expanding on this example it is easy to see the growth can be expendintial which will eat up your hdd alot faster as the database gets bigger. This setting should be considered carefully for both MDF and LDF. You should choose the growth value by MB and base it on your experince on how much it grows. You want it large enough so it is not having to expand continously to keep up with the demand.
On a 60GB dB, with Log file of 18GB; I hae set the Log Expansion to 512MB. Thanks.
Bravooo!!! Bravoo!!! Mohit :-D.
I am sure the OP would never ever think of %'age auto grow again....
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply