How does SharePoint Farm Reporter (ARK for SharePoint) manage the application database every time we generate a new report?
Applies to: SharePoint Farm Reporter 2.x and above
SharePoint Farm Reporter (formerly known as ARK for SharePoint) creates a single application database in the SQL Server for both Quick Reports and Power Reports features. In case of Power Reports, SharePoint Farm Reporter creates a new table corresponding to the report in the application database for every report task execution instance. SharePoint Farm Reporter will delete only the report’s table after the report is generated successfully.
The application database must be deleted manually after uninstalling SharePoint Farm Reporter application. Additionally, if the application process terminates or fails for any unknown reason, the database trace may remain in the SQL Server.
In addition to database deletion, which you can do it after you uninstall the product, you can perform a database cleanup periodically.
Some of the reports in SharePoint Farm Reporter perform more transactions in the database, which may result in increase in its database file size. As for the cleanup, SharePoint Farm Reporter can truncate and shrink the application database related files based on the settings provided in Application Database Truncation Settings while closing the application. To configure the settings, go to Tools menu and click Application Database Truncation Settings in SharePoint Farm Reporter. After shrinking the database, SharePoint Farm Reporter will collect the report data afresh during the next report request.
You can also cleanup the SharePoint Farm Reporter tables corresponding to a task or a task history instance using the ‘Remove all’ or ‘Remove’ or ‘Cleanup History’ options in SharePoint Farm Reporter application.
Please refer to the following MSDN articles to manage database disk space in SQL Server:
Shrinking a Database https://msdn.microsoft.com/en-us/library/ms189080.aspx
How to: Shrink a Database (SQL Server Management Studio) https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database