How to check the transaction log size in SQL Server

Question : Is the Log Size on DBCC SQLPERF(LOGSPACE) the max size the log file has ever been or an amount of reserved space?

Answer: It is the current size allocated to the transaction log. It isn’t the max size. You can get the max size from the sys.database_files max_size column.

            You have 3 main variables to manipulate at the CREATE DATABASE or ALTER DATABASE

                          Size

                         Growth rate

                         Max size

There are some considerations in setting the MAX SIZE. Let’s say you’re in the middle of a transaction and the log file limit is reached , the in-flight transaction will not be able to COMMIT and errors will appear. Log file sizing and growth parameters need to be set with flexibility to support activity in between the CHECKPOINT step. Defining low maximum limits or maintaining transaction logs on insufficient disk space will increase the risk of an outage

As an added note , setting a database recovery model to SIMPLE does not guarantee avoiding running out of disk space. A long running transaction with a COMMIT can leady to a situation of disk space saturation. A typical example is The transaction log for database is full due to ACTIVE_TRANSACTION

How can we tell the max size a log file disk should be? Estimating the maximum size to set the disk supporting will rely a combination of your understanding on the transactions executing and the impact on the transaction log size. Another factor is how you manage the LOG BACKUP frequency .

 Use the guidelines outlined in  SQL Server Install Checklist for some database initial sizes and growth rates.

Read More

Is a LOG BACKUP allowed on a SIMPLE RECOVERY database ...