Estimating transaction log size

Question

Estimating transaction log size

alark

Default port

Points: 1402
More actions
August 7, 2008 at 11:25 am

#126911

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

RPSQL Hall of Fame Points: 3229 More actions · Answer 1

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...

alark Default port Points: 1402 More actions · Answer 2

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?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

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.

alark Default port Points: 1402 More actions · Answer 5

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?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

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.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 7

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.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 8

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

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.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

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.

alark Default port Points: 1402 More actions · Answer 11

Thanks everyone for your help.

After adjusting transaction log backup frequency transaction logs size is finally under control.