We have been noticing that the size of our SQL database, and particularly the size of the transaction log (*.ldf file), has been growing very large lately. We would like to delete a few months of data in order to reduce this size and prevent it from growing in the future. What recommendation would you give us?
Promodag Reports does not manage the size of your database. This can be done using SQL Server management tools (Enterprise Manager, SQL Server Management Studio). You will most probably find out that a large percentage of disk space used by your database data files is actually unused (see screenshot below). You must shrink them to reclaim allocated, but unused space.
So, our first recommendation would be
- to shrink your transaction log file, and then
- to apply the Simple recovery model to your SQL database in order to prevent it from getting very big. For more information, see our online help: Using Promodag Reports and Microsoft SQL Server.
If you decide to purge a large volume of data, e.g. several months, use the Purge Message Tracking, Purge IIS Data and Purge Storage Size Data
options in the Tools
menu and clear old data by periods of two weeks. This action will inflate the transaction log and we recommend you to shrink the transaction log between each period.
Both data purge and database compaction/shrink can be automated (see link below).