(877) 456-7632 info@msxgroup.com
Message Boards
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Derek.Krebs

Moderator
Registered:
Posts: 105
Reply with quote  #1 

The Microsoft Forecaster database can grow very large over time and there are things in both Forecaster and SQL that may improve performance and reduce the database size.  Bullet point items to check would include

 

1.       Zap history.  Check the Audit Trail History table.  This table tracks nearly every data-related change in the system.  It can grow really, really large, very rapidly.  If the customer is not looking at older history, they can clear it out.  Do this also on the Zap Wizard, "Tools | Zap".  Choose "History" on the first screen, then on the second screen choose "Audit Trail History" in the History to delete section.  In the Timeframe section of the second screen, choose either "Clear all data" to remove all of the audit trail history, or choose the "Clear data on or before" option and then specify a date in the calendar. All history prior to and including the date specified will be removed when you complete the wizard.

 

2.       Do SQL backup.  Usually a log file growing to that size implies a SQL database backup may not have been done in a while.

 

3.       Event log (View | Event Log in Forecaster).  Is there anything in there?  If so, what (event log grid can be copy/pasted to Excel)?

 

4.       Indexes.  Did you turn on in SQL Server the "Auto Create/Auto Update Statistics" options?  These options should be OFF, as they attempt to make indexes that SQL Server thinks are needed… Forecaster adds and removes indexes as needed for various processes and if SQL Server is doing the same thing it can cause conflicts.

 

5.       Do you run transactional backups of the database?  If NOT, then make sure the database recovery model is set to Simple.

 

6.       Reindex within Forecaster:  After ensuring the Auto Create/Update statistics options are disabled in SQL Server, go to Tools | Validate Database and run only the re-index and clear event log options.  Do not run the validate segments or periods options.  This may take some time to run, so please run it when system load is lower, like overnight.  Reindexing typically only takes a few minutes to complete, but can go longer if the database is large.

 

7.     Zap summary data.  Some tables are considered "volatile" in that they can be cleared and rebuilt simply from the front end.  The summary data tables are some of these.  You can clear these out before updating the periods, then repopulate them by running a restate afterwards.  To clear the summary data tables, go to Tools | Zap, which will launch the Zap Wizard.  Choose the "Data" option on the first screen, then on the second screen choose "Account Data" and "Summary Data" (in the Data to Zap dropdown list).  Complete the wizard – on the final confirmation screen it should only say "Clear all Summary-level data".  Once this is done, go to Data | Restate to populate these summary tables.

 

 

Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.