Shrink the size of the SolarWinds Platform Database Transaction Log File

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

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 the 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.

This topic applies to all supported versions of SolarWinds Platform and modules that use a SolarWinds Platform database, more details here in Currently supported software versions.

***IMPORTANT***

SolarWinds strongly recommends that you create a full SQL backup of your SolarWinds Platform 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. See SQL Management Studio Direct Download Link.

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. RDP to Main Poller
2. Stop all services using the SolarWinds Platform Service Manager.
3. Open the SQL Server Management Studio and connect to your SQL server.
4. Right-click the SolarWinds Platform Database and select All Tasks > Shrink > Files.
5. Set the File Type to Log. 

6. Under the Shrink action, select the option to Reorganize pages before releasing unused space. 
7. Set the Shrink File to 0 MB, and then click OK. 

8. It may be necessary to run the shrink several times
9. Start all services using the SolarWinds Platform Service Manager
10. The Transaction Log File should now have a decreased size. 

Obs: You can use the following query to get the progress of the Shrink task. (it's possible to run using Database Manager)

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT 
    percent_complete, 
    dateadd(second,estimated_completion_time/ 1000, getdate()) as est_completion_time
FROM 
    sys.dm_exec_requests
WHERE
    command = 'DbccFilesCompact'

Shrink the Transaction Log File for a database in FULL recovery mode  (Availability Groups)

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. In the SQL Server Management Studio, perform the FULL DB backup.
2. Run the CHECKPOINT command.
3. Perform LOG backup two or three times
4. Shrink the transaction log file as described above.
5. When done, configure the default size of the transaction log and the auto-growth. 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.