August 5, 2010 at 1:02 pm
Hi,
I have a SQL 2005 DB that the log file (LDF) is growing too large (over 25GB). The Autogrowth is set to: File Growth in 10% and Maximum File Size Restricted to 2,097,152 MB; also the recovery mode is set to Full. Any idea on why the system is not managing the log file growth to the restricted limit which is about 2GB or is this normal?
Thanks in advance
August 5, 2010 at 1:14 pm
This is normal if you have databases that are running long/big transactions.
run dbcc opentran to see which transactions are currently running.
Rebuilding/Reorganizing indexes will also increase the log if the log is not backed up in time.
August 5, 2010 at 1:24 pm
Alex thanks. The DB is backed up everyday at night. So if I understand you correctly does that mean once the DB is backed up, the system is smart enough to recognize that the DB is backed-up and shrink itself to the restricted limit?
I just run dbcc opentran and No active open transactions are running
The DB maintenance (rebuild/defrag reindex and stat update) is done once a week (Sundays) at night.
August 5, 2010 at 1:33 pm
ejbatu1 (8/5/2010)
Hi,I have a SQL 2005 DB that the log file (LDF) is growing too large (over 25GB). The Autogrowth is set to: File Growth in 10% and Maximum File Size Restricted to 2,097,152 MB; also the recovery mode is set to Full. Any idea on why the system is not managing the log file growth to the restricted limit which is about 2GB or is this normal?
Thanks in advance
Please read the maximum file size is 2,097,152 MB not 2 GB (2048 MB). Please schedule a transaction log backup at regular intervals and the transaction log file size will be under control.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 5, 2010 at 1:37 pm
ejbatu1 (8/5/2010)
Alex thanks. The DB is backed up everyday at night. So if I understand you correctly does that mean once the DB is backed up, the system is smart enough to recognize that the DB is backed-up and shrink itself to the restricted limit?
besides backing up databases you also have to backup transaction logs.
Once you backup tran logs they will be empty again but they will not shrink.
August 5, 2010 at 1:45 pm
Okay thank you both... What is the difference between shrinking and emptying?
Also, how often do you recommend the backup to be scheduled?
August 5, 2010 at 2:42 pm
"Emptying"/backing up the transaction log makes the inactive part of the transaction log available again to store more transactions. so before a backup your active transaction log will grow and grow. when you back it up it becomes inactive.
Shrinking actually reduces the physical disk size of the transaction log and makes the space available to the OS. It is not recommended that you shrink your transaction log (or database for that matter) as it will probably just regrow again causing extra overhead on the server.
August 5, 2010 at 2:44 pm
regarding how often you back up the t-log that depends on your environment. you need to ask yourself how much you can afford the log to grow, how often you want to have a point in time backup available to restore but also balance it against not having too many files that you need to restore in the event of an emergency.
Let me dig out gails article, that explains it a lot better than i just have 🙂
edit - here you go http://qa.sqlservercentral.com/articles/64582/
August 6, 2010 at 9:06 am
Thank you that is helpful
August 9, 2010 at 9:39 am
I had another question on this... is backing up the log file (overwrite mode) every two hours and backing up the DB once a day (at night) a good idea, or should the log file backup be done once a day too?
August 9, 2010 at 9:45 am
The interval between log backups should be the maximum allowable data loss for the database. If you have no need of point-in-time recovery (restoring to point of failure), rather set the database to simple recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2010 at 10:15 am
ejbatu1 (8/9/2010)
I had another question on this... is backing up the log file (overwrite mode) every two hours and backing up the DB once a day (at night) a good idea, or should the log file backup be done once a day too?
Determine the priority & importance of the data to your business, then create a backup strategy that fits. There is not a single answer.
If you overwrite your logs backups, then you can't use them to restore in case of emergency.
For production databases, I backup every 15 min, and delete old log backups after a week. They are also on tape, so I can go back 4 weeks if needed.
If it's non-production, or fairly static data, then maybe a daily full backup in 'simple' mode is enough.
August 10, 2010 at 7:03 am
ejbatu1 (8/9/2010)
I had another question on this... is backing up the log file (overwrite mode) every two hours and backing up the DB once a day (at night) a good idea, or should the log file backup be done once a day too?
If you are working on anything other than homework here (like real production data for a company for instance) I HIGHLY recommend you get a professional in to review your systems and get things set up right. You are asking questions that someone responsible for company data should not be asking. Nothing personal there - just giving you my best advice. Your company's data is most definitely at risk.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply