For database administrators working with the report database utility you will notice the program has an affect on the size of the database you are working in. Based on the style of database (SQL or ACCESS) you are working with you are affected differently. Below is a review of each style and the impact the report database utility has:
MICROSOFT ACCESS – Running the Report Database Utility on an MS ACCESS database will increase the size of the database until you compact the database file and it will come back down in size. When you run the Report Database Utility on an MS ACCESS database the utility runs queries in the database to export the data to a new MDB file. Those exports are handled using temporary data in MS ACCESS and that temporary ‘garbage data’ is not managed as efficiently as it might be by MS ACCESS, and the temporary space is what causes the growth in size of the WSD file you are running the Report Database Utility on. When you use the WS ADMINISTATOR to OPTIMIZE/COMPACT the database the WSD file will shrink back down to about the size it was before running the Report Database Utility. As a part of the process of running the Report Database Utility you should optimize the database when you are finished (ideally).
SQL SERVER – Running the Report Database Utility on an MS SQL SERVER database will cause transactions to be logged in your WINSALES database transaction LOG file in SQL Server. If you have a large SQL SERVER WINSALES database recreating the entire reporting database will cause thousands of transactions to be created. Running the Report Database Utility daily on a large SQL SERVER database can cause enough transactions to cause the LOG file in SQL to grow substantially. Dropping the LOG file is the best work around to large LOG files. In the Report Database Utility you have the option in SQL to re-run the stored procedures created to automate the utility, and if you run the REFRESH WSRPT instead of MAKE WSRPT, you will only get changes that have taken place in the database since the last run, not a complete recreation of the WSRPT tables, which results in fewer transactions.
Contact Silent Partner for more information.