MSSQLTips

Guys I have a question in regards to database tranaction log size.  Ok let's say I create a 10 gig database my question is how big should the initial tranaction log be? I know the log growth rate is set at 10 percent Is there some type of formula that allows you to calculate the log size for new databases for example a 20 gig database gets a 2 gig log. I know that log size is dependent on the database activity however I'm just curious in knowing of theres a base line.  How do you all handle this do you just leave the defaults watch the database over time than adjust accordingly.

no rule but I usually configure log file at 25% of datafile and I always create a database in SIMPLE mode.  I only change to FULL if the business requires point in time recovery.  Obviously, a database in FULL mode requires a log file to be sized more intelligently.

 I'm using database mirroring so I'm required to run full recovery mode. I was on technet and one guy stated that he normally does 10 to 20 percent this really seems like it's a guessing game. 

If you are running database mirroring then you will need a log file that is large enough to handle your largest transaction.  Large BULK insert/update and delete operations have the potential to consume large amounts of transaction logs as well as maintenance operations like index rebuilds.

Additionally, if you are running mirroring in asynch mode over a wan with a low bandwidth link then transactions will stay in the transaction log of the principal untill they are applied on the mirror.  I have seen databases with transaction logs approaching 100GB used space on the principal even with frequent log backups.

 One thing to bear in mind with mirroring is that you sill need to backup your transaction log on the principal in order to truncate the file.  Some installations forget to do this.

 More frequent log backups can usually mean smaller log file requirements.