The Blue Mountain RAM application records nearly every transaction that flows through the database layer in a table known as the “Transaction Log Table.” As system usage occurs over time, the data stored in this table becomes stale and can unnecessarily bloat the size of the database. In this blog series, we will uncover the reasons for database growth, as well as review the maintenance procedures that can minimize file size and growth, optimize general performance, and facilitate performance analysis.
Benefits of optimizing your database:
- Available transaction metrics (a tool for Blue Mountain to conduct any necessary performance analysis of the actual usage of your system)
- Defragmented and optimized database files (.mdf/.ldf)
- Minimized data file and backup file sizes
- Improved performance of backup and restore operations
- Improved potential general system performance
Reasons why you should optimize your database:
- Preserve valuable transactions metrics. Blue Mountain has implemented the “Application Truncation Log Metrics and Truncation” in order to retain the value of the Transaction Log data, while minimizing its impact on database size. This application runs on a monthly schedule to calculate and record metrics for the application transactions. The aged transaction data is then either truncated or discarded to minimize the space required to store it; resulting in a significantly slowed database growth rate. A stored procedure such as this often aids in various performance analysis tasks. By preserving these valuable transaction metrics, Blue Mountain RAM is able to more precisely implement performance enhancements in future releases.
- Clean up excess space that is no longer needed for the “Transaction Log Table.” By performing “right-sizing” on your SQL Server Database file, Transaction Log file sizes, and the auto-growth settings on each file, you can achieve general performance improvements in the system. If your database has already been in use for some time, the excess space that is no longer needed for the “Transaction Log Table” will not automatically be reclaimed. Optimal size and growth settings of the SQL Server data and transaction log files (.mdf/.ldf) will result in the minimization of frequency of database growth operations, physical and logical fragmentation of the files, size of backup files, and the time required to create or restore them.
- Maintain consistent data file growth with proper database and transaction log backup operations. Determining an ideal size and file growth for the data and log files will vary for each system. Ideally, once the shrink operation has occurred, a data file (.mdf) size should be chosen that is at least 5GB greater than the current size of it on disk. This will ensure that there is room for growth while the transaction log file (.ldf) is sized to a projected maximum value. If the log file is sized appropriately, it rarely needs to grow again as it reclaims its own space. Additionally, a reasonable file growth value (512MB-1024MB minimum) for both the data and log files should be chosen so that both files ideally need to grow infrequently. Although larger growth values also help to minimize fragmentation, excessively large growth values can potentially cause extra overhead when the growth operation is triggered.
Ultimately, optimizing your database provides you with many benefits. You can reach your greatest return on investment and ensure the performance of your data files. Stay tuned for our second blog in this series where we will discuss the necessary steps on how to optimize your Blue Mountain RAM database.
If you would like to execute this process, contact your Technical Support team at firstname.lastname@example.org for the necessary documentation and scripts.