sql server transaction log file size

Question

Hello,

We have a sql database that has a transaction file size set to 64264MB on the database options. This file seems rather large for what I'm seeing, and is taking up some perhaps unneeded space. Transaction log backups are happening hourly.

The results of the sql query: dbcc sqlperf(logspace) show the space used in the log file is 3.38% of the file size just before the transaction log backup is made. After the backup is made the command was run again, and the % of space used is much lower, as expected.

My questions are:

1.) The setting for the transaction file 'size' in the database options is the minimum size that file will ever get, correct? So I will never see a log file size smaller than 64GB. Correct?
2.) Is the dbcc sqlperf(logspace) query an appropriate measure for adjusting and setting the log file size on the database options? It appears the file could be adjusted to 4GB without having to grow the file.

Answer 1

The setting for the transaction file 'size' in the database options is the minimum size that file will ever get, correct?
Yes, unless the logfile "size" in the database properties under files is adjusted, the actual value should never go below this amount after backups, restarts, etc.

Is the dbcc sqlperf(logspace) query an appropriate measure for adjusting and setting the log file size on the database options?
Yes, but keep in mind that is a only a snapshot of the file at that point in time. Getting a history of the command over time and at the application's peak usage would be a better indicator of how much space the log really needs to prevent it having to grow.

So I will never see a log file size smaller than 64GB. Correct?
Correct. If you feel this is inflated, and the database log will never need to grow to this amount, it could be adjusted smaller as you say to 4gb. The point of this setting is to avoid a performance hit should the file need to ever grow which setting it at a high value initially prevents this.

Answer 2

Hi @Tyler Lachaussie ,

Is the dbcc sqlperf(logspace) query an appropriate measure for adjusting and setting the log file size on the database options?

Agree with them.
It is recommended to set the initial size and the auto-growth of the Transaction Log file to reasonable values. Although there is no one optimal value for Transaction Log File initial size and auto-growth that fits all situations, but setting the initial size of the SQL Server Transaction Log file to 20-30% of the database data file size and the auto-growth to a large amount, above 1024MB, based on your database growth plan can be considered as a good starting point for the normal workload. In this way, we will avoid the problem of the frequent and the small increments in the SQL Transaction Log file size, that may lead to queries slowness, due to pausing all log writing processes until the log file size increase process is completed, and generates a large number of Virtual Log Files.
One way to keep an eye on your log is to use the related performance counters exposed from SQL Server. This can be achieved with a similar query:

select * from sys.dm_os_performance_counters
where counter_name in ('Log Growths','Log Shrinks','Percent Log Used','Log Flush Waits/sec','Log Bytes Flushed/sec','Log Flushes/sec','Log Truncations')
and instance_name='your_database'

For more details, please see this article:
https://www.sqlshack.com/sql-server-transaction-log-part-3-configuration-best-practices/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.