Sql-server – Autogrowth and Initial Size with T-Log

availability-groupssql server 2014transaction-log

Please help me in understanding the below issue :

SQL Server Version 2014 with AOAGs. We have separate Data , Log , temp DB , SQLFS Drives individually.

Our Backup Strategy is as below:

  • Weekly Full Backup
  • Daily Differential
  • Every 15 Minutes Log backup

My Total Log drive space is 300 GB.
When I checked the DBCC SQLPERF(LOGSPACE) only tempdb is highest consumer.

AutoGrowth Settings :

enter image description here

NOTE : Currently I have only 20% free space left. Autogrowth is in '%'.

Also we have Filestream data enabled . Will Filestream have separate Log file ?

Result of T-Log utilization:

enter image description here

Please help me with below concerns as I am new to SQL Server DBA.

  1. In what case I should go for increasing my Log drive space . For ex: E:\ is for my SQLLOG, in what case I should expand or increase my drive. What is the end point I should consider before expanding drive ?
  2. Sometimes DBCC Shrink does not free up much space ! what could be the valid reason.
  3. And I read some articles where it is mentioned not to use Autogrowth in '%' instead we should use in 'MB's'. I have mentioned my case above . Kindly correct me if I am in good lines or false affect. Please correct me and help me on going for good considerations.

Thanks

Best Answer

I think you can calculate each 15 min time slot before doing transaction backup, because after transaction backup, the previous transaction logs will be rewriteable, and that will be the maximum of the transaction log file size.

DBCC Shrinkfile sometimes could not work well because there are some transaction not write down to database already (transaction will be wrote to log file first and then write to database), you can wait a while and DBCC again.

Related Question