August 7, 2008 at 11:25 am
All our databases inFull Recovery model were created with the default setting for transaction log to grow by 10%, restricted to 2,097,152 Mb. I run trans log backups every 2 hours, but the logs still grow sometimes x-times bigger than .mdf files! According to BOL the size of the trans log should be 25% of the data file.
Question: How do I achieve this size? If my datafile is 10Gb, then do I need to change the Autogrowth option by setting restricted growth to 2.5 Gb or I change the initial size? Then what will happen if the log file will hit this limit?
Can anybody shed the light on this please?
Thanks in advance
August 7, 2008 at 12:33 pm
It seems that in your application there are many open transactions by times i.e. it is a very busy database. What you need to do here is create a job which shrink log files on that database time by time...
August 7, 2008 at 2:13 pm
I know this option and I do it sometimes, but there is a common opinion that we shouldn't shrink our logs too often since it fragments the file. So, are there any other means to control the translog file size?
August 7, 2008 at 2:53 pm
There is a medicine curing all. Your log file increases because of heavy activities there. You cannot set a limit for it. You may try to use BULK_LOGGED to reduce the logs by bulk insert activities if there are.
August 7, 2008 at 3:14 pm
You should run transaction log backups a lot more often.
I usually set them up to run every 15 minutes, but run them as often as every 5 minutes if there is a lot of activity.
August 7, 2008 at 3:24 pm
Mike,
Thank you for the advice, I will change trans log bkp to 15 min instead of 2 hours.
Please clarify for me: In order to have a trans log file 25% the size of data file you don't set its initial size or autogrowth to 25%. You achive it by adjusting the frequency of backup, right? Or anything else?
August 7, 2008 at 3:59 pm
You need to have transaction log backups often to control the size of the transaction log, expecially if you are doing large batch operations, like reindexing, index defrag, or big batch imports. Also, it will minimize data loss in the event of the need to do a recovery.
August 7, 2008 at 11:24 pm
In addition to regularly backing up the transaction log, you need to ensure that transactions are committed or rolled back as soon as possible. Otherwise, the active portion of the log will keep growing in size. Run DBCC OPENTRAN to identify the current oldest active transaction. See this document for further details on the transaction log.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 8, 2008 at 12:25 am
I would say that change the recovery model to SIMPLE, before that take a full backup of your database and as Mike said decrease the time limit for transaction log backup and also go for differential backup with 2-4 hrs interval
Abhijit - http://abhijitmore.wordpress.com
August 8, 2008 at 1:21 pm
Abhijit More (8/8/2008)
I would say that change the recovery model to SIMPLE, before that take a full backup of your database and as Mike said decrease the time limit for transaction log backup and also go for differential backup with 2-4 hrs interval
That is wrong. If you change the recovery mode to SIMPLE, you cannot do transaction log backups.
August 10, 2008 at 5:29 pm
Unless you have a good reason, and understand it, you SHOULD NOT set a production database to simple mode.
The transaction log is essentially a copy of all the work done in your database, all adds, changes, deletes, that provides integrity and assurance you have a database that is intact with the correct data based on work done. Even if power fails in the middle of a transaction.
You should not set a size for your log as xx% of the data. It is likely smaller than your data size, but I have seen databases with heavy change activity and little adds/deletes, have a log that is larger.
A log backup, while providing recovery, clears out committed transactions from the log, allowing the space in the log to be reused. More frequent log backups will allow you to have a smaller log.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 15, 2008 at 12:38 pm
Thanks everyone for your help.
After adjusting transaction log backup frequency transaction logs size is finally under control.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply