In this article, we will go through a number of SQL Server Transaction Log recommendations and best practices that each database administrator needs to follow in order to enjoy his holidays and have a pleasant sleep without nightmares or calls during the night. Before reading this article, it is recommended to check the previous articles of the SQL Server Transaction Log series (see the TOC below), in order to be familiar with the different SQL Server Transaction Log related concepts that you will meet in this article. Transaction Log File Size When a user database is created, the initial size and the auto-growth settings of the SQL Server Transaction Log file will replicate the initial size and auto-growth settings of themodelsystem database, also known as Template database. By default, the SQL Transaction Log file initial size of a newly created database is 8MB, with the auto-growth amount of 64MB, as shown in the Files page of the Database Properties window ge below: 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. Keep in mind also that the Transaction Log file cannot take advantages from theInstant File Initialization option, that makes the Transaction Log file extending process slower. Setting the SQL Server Transaction Log file initial size for the tempdb system database with a proper large amount should be considered from the beginning. This is due to the fact that, restarting the SQL Server instance will drop the tempdb data and log files and create it again with the original predefined initial size and auto-growth amount. If the SQL Transaction Log file of the tempdb is configured with small initial size, the extra overhead that is caused by increasing the size of thetempdbTransaction Log will affect the performance of the tempdb database. Another factor that should be taken into consideration is the maximum size that the SQL Server Transaction Log file can reach. Two things should be considered when setting the maximum size amount; preventing the Transaction Log file from filling the disk drive and protecting the database from being inaccessible when the Transaction Log file runs out of free space, as follows: After providing the proper values for the initial size, auto-growth, and maximum file size, we should keep an eye on the SQL Server Transaction Log file and monitor the database file growth while handling the incoming workload, and tune it based on that growth amount. The sys.dm_db_log_space_usage DMV can be used to monitor the log space in a specific database. It provides information about the amount of log space currently used and if the SQL transaction log needs truncation: Note: For more information about the operations that cause the Transaction log file to grow and how to monitor and manage it, check SQL Server Transaction Log Growth Monitoring and Management. Multiple Transaction Log Files SQL Server provides us with the ability to create more than one SQL Transaction Log file on each database. But we should distinguish between the ability and the advantages of having more than one SQL Server Transaction Log file in your database. This decision is built mainly on the architecture of the Transaction Log file and the fact that the transaction logs are written in sequentia

sqlshack.com