Submit a ticketCall us

Training ClassThe Orion® Platform Instructor-led Classes

Provided by SolarWinds® Academy, these trainings will introduce users to the Orion Platform and its features, management, and navigation. These courses are suitable for users looking to discover new tips, tricks, and ways to adapt their Orion products to better suit their monitoring needs:
Deploying the Orion Platform
Configuring Orion views, maps, and accounts
Configuring Orion alerts and reports

Reserve your seat.

Home > Success Center > Orion Platform > Orion - Knowledgebase Articles > Shrink the size of the Orion Database Transaction Log File

Shrink the size of the Orion Database Transaction Log File

Updated: January 30, 2019

Overview

This article describes how you can decrease the size of the SolarWinds Orion Database by reducing the Transaction Log File.

SolarWinds recommends that you only shrink transaction log files. Do not shrink data files because it might result in fragmented indices, slow performance, data loss, and problems with writing data to the database.

This article is also part of Quick Orion database health check guide.

See Optimize the data you retain in the database for details about alternative ways to decrease your database size.

Environment

Steps

  • SolarWinds strongly recommends that you create a full SQL backup of your SolarWinds Orion database. The following procedure may remove all your existing data and modifications. 
  • Shrinking the database causes fragmentation of the indexes, which can cause performance issues. The best practice is after you shrink the database you also run a reindexing job against the database to improve the performance.

  • If you don't have Microsoft SQL Server Management Studio, you can download and install SQL Server Management Studio Express from Microsoft for free.

Shrink the Transaction Log File - SIMPLE recovery mode

If your database is in the SIMPLE recovery mode, consider shrinking the transaction log if it is larger than 10-15 % of the space used up by data files.

Make sure that you have Autogrowth set appropriately to your database size (for example, 1024 MB in case of smaller databases, and 8 GB for large databases).

  1. Stop all Orion Services using the Orion Service Manager.
  2. Open the SQL Server Management Studio and connect to your SQL server.
  3. Right-click the Orion Database and select All Tasks > Shrink > Files.
  4. Set the File Type to Log
    The Filegroup is not an available option.
  5. Under the Shrink action, select the option to Reorganize pages before releasing unused space. 
  6. Set the Shrink File to 0 MB, and then click OK. 

    This step may take a while to complete. A few minutes per GB. 

  7. It may be necessary to run the shrink several times.
  8. Restart all Orion Services. The transaction Log file should now have a decreased size. 

Shrink the Transaction Log File for a database in FULL recovery mode

Shrinking the Transaction Log File does not work if the database is set to Full Recovery mode. This mode causes all transactions to be stored in the Transaction Log File to allow the DB to recover the last entries when required. A simple recovery will only allow recovery up to the last full backup.

If your database is in the FULL recovery mode, the transaction log might grow all the time. This occurs if you do not make regular backups of the transaction log. Shrinking the log will not help even if you start making backups every hour, or even every 15 minutes. It is because the pre-backups part stays as huge as it was.
To resolve the situation:

  1. Make sure there are no transactions running on the database.
  2. If you have Availability Groups defined, remove the database from Availability Groups.
  3. Switch the database recovery mode to SIMPLE recovery.
  4. In the Management Studio, run the CHECKPOINT command. Run it twice, in succession, just to be sure.
  5. Shrink the transaction log file as described above.
  6. When done, configure the default size of the transaction log and the autogrowth.
    These values depend on your database size. For small databases, you can use 2048 MB for Transaction Log Size and 1024 MB for Autogrowth. For larger databases, consider using 8 GB for both settings.
  7. Add the database back to Availability Groups.
  8. Switch the database back to the FULL recovery mode.
  9. Set up a job to regularly run the backup of the transaction log (so that you don't end up with a huge transaction log as you had at the beginning).

 

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

 

Last modified

Tags

Classifications

Public