SQL database: How to reduce the size of your database

Question:

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?

Answer:

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
  1. to shrink your transaction log file, and then
  2. to apply the Simple recovery model to your SQL database in order to prevent it from getting very big. For more information, see: 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/IIS/Storage Size Data  options under the Tools menu and clear old data by chunks of 2 weeks. This action will inflate the transaction log and we recommend you to shrink the transaction log between each chunk.

Both data purge and database compaction/shrink can be automated (see link below).

See also:

Add Feedback