Report Manager - Disk size growing too large

Last Modified

Tue Jun 18 20:42 GMT 2019

Description

  • Report Manager regularly receives data from N-able N-central to populate its database.
  • During this process Report Manager will run various tasks to move data from one database to another.
  • Sometimes as a result of the entire process or the Report Manager retention settings the disk size will grow beyond expectation.

Environment

  • N-able N-central
  • Report Manager

Solution

  • Disk size growth can be caused by any issue below (not limited to just these):
    • The Data Retention settings are set too long and are retaining more Data than intended.
      • if the Data Retention settings are set to long or to keep all data the database will grow beyond what is expected.
      • If the Data Retention settings are set keep all data than the database will grow indefinitely.
    • Log files are growing too large.
      • The transaction logs for SQL will grow and shrink periodically when running the various steps in the Export/ETL processes.
      • Typically this happens when rows are deleted.
      • It is important to make sure there is enough free space on the drive for this to complete however it should be noted that if something is wrong in the database the logs have the potential to balloon.
    • TEMPDB
      • Like the transaction logs this temporary database will grow and shrink during the Export/ETL process.
      • Enough space should exist for this but if its not shrinking or expanding beyond expectations this could be an indication of an issue with the ETL or export.
    • Data Retention Settings:
      • First verify that the databases are actually what are taking up the large amount of space.
        • Check your database file locations (mdf files) or in sql right click each database and look at the storage properties.
        • Check to ensure that the log files are not what is causing your space issues. look at the log file locations (ldf files) and check to ensure there isnt a problem with transaction log growth.
      • Log in to the Report Manager Administration Console and go to system settings and logs and change the settings to the desired retention period.
      • Be careful when setting the retention period as the maintenance task will now attempt to cleanup your data by running deletes if you make too large of a change you may risk filling up your drive when the maintenance task runs.
        • To avoid this set the retention settings to smaller increment.
        • For example if you are set to keep 24 months of data and want to go to 12 months of data first go to 18 months then wait a few days then reduce to 12 months.
        • This will give the maintenance tasks the opportunity to recover space in smaller increments. It takes space to recover it.
      • After the time has elapsed you may need to shrink the Database file using the shrink task, this should not be preformed on a regular basis as it can lead to inefficiencies in the database.
      • Delete Unused ODS data:
        • Deleting unused ODS data will truncate data tables not used in stock reporting in order to conserve disk space.
        • After data is removed from the ODS, it cannot be restored.
        • Selecting this option may impact the availability of historical data for generating new reports in future releases.
        • In addition, data truncation may prevent the generation of custom reports that reference the raw data tables in the ODS.
        • This does not affect any custom reports that reference data from the data warehouse.
        • If this option is unchecked it will take a significant amount of additional space.
        • To verify what tables will be purged you can run the query below:
select tablename from config.dbo.dataretentionsettings where isexported = 0 order by tablename
  • If after changing the settings and waiting for the space to recover there is still an issue please engage support and support can assist in manually recovering the space.
  • Log Files:
    • Check the log files to see if they have grown beyond expectation.
      • Log files should not grow much beyond a few GB's depending on the size of your database.
      • If the logs seem excessive you can try to use the shrink task to shrink them back to 1mb and see if that will recover the space.
      • If after shrinking there is still an issue this is likely indicative of a greater problem and technical support should be engaged.
  • TEMPDB
    • Same as the log files both this databases log files and database can be shrunk as long as there are no active ETL's.
      • Check Task Manager to see if there is a process running called 'DTEXEC". if not you can attempt to shrink both the database files and log files.
      • If this continuously happens and does not shrink on its own, or stays at a large capacity (this should be in the very low GB's) then support should be engaged.