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. You must shrink them to reclaim allocated, but unused space.

 

So, our first recommendation would be

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 using the Tools > Task Automation option. For more information, see the help file, Using Promodag Reports.

Related articles:

• Using Promodag Reports and Microsoft SQL Server
• How to manually purge data from your database