Furthering our knowledge of the Blue Mountain RAM Database, from the reasons for database growth to the optimization of general performance and facilitation of performance analysis, this second blog in our ‘Optimizing Blue Mountain RAM Database’ series will cover the necessary steps needed to optimize your database. Let’s begin by reviewing the maintenance procedures needed to minimize file size and growth.
Steps To Optimize Your Database:
- Perform a Full Database backup.
- Execute the DBCC OPENTRAN command. This will check for potential open transactions. There should not be any open transactions existing before proceeding.
- Temporarily switch the Recovery Model to BULK_LOGGED. This allows script to run more efficiently.
- Temporarily increase the size and growth values of the data. Log files by doubling their current values to allow the following script to run more efficiently.
- Execute the “BatchTransactionLogTruncation.sql” script. This allows you to perform the metrics calculation and truncation operation. Note that this script may take hours to complete.
- Temporarily switch the Recovery Model to SIMPLE. This will allow the subsequent shrink database operation to successfully shrink both the data and log files.
- Execute the DBCC SHRINKDATABASE command on the database.
- Execute the “AdjustIndexes.sql” script. This rebuilds all indexes which have now been fragmented by the shrink database operation.
- Set the size and growth values of both the data and log files to appropriate values.
- Switch the Recovery Model back to the original value (FULL or SIMPLE).
- Create a scheduled SQL Job to execute the existing stored procedure in the database. This will perform the metrics calculation and truncation operation on a monthly basis.
Following the procedures described above ensures the optimization of your database. The best part of this process is that the steps only need to be performed once. By optimizing your Blue Mountain RAM database, you will see many benefits. Such include available transaction metrics for performance analysis, accessible defragmented and optimized database files (.mdf/.ldf), minimized data and backup file sizes, improved backup and restore operations performance, and enhanced general system performance.
If you would like to execute this process, contact your Technical Support team at firstname.lastname@example.org for the necessary documentation and scripts.