...
All those Actions can be done using the SSIS Maintenance Plan designer.
They should be planned in period of server inactivity. This is obviously not always possible.
In the case that you can't afford a big server slow down, use the scripts attached to the wiki to delay the update of statistics and defrag of indexes. Those script will let the server catch up in between operations. They can be customized at will. If you know some tables in there are really getting big, you can add WAITFOR Statements to further slow down the defrag/statistics update and let Proficy catch up with the pending tasks.
*It cannot stress enough that your maintenance plan will need to be tested. You will need to execute the maintenance tasks manually during "normal hours" and monitor the server. This can be done on a TEST or DEVELOPMENT server to get a good idea of how it will behave on the Production database. Use the task manager, watch for pending tasks, watch for server locks, deadlocks and so on. Use a SQL Profiler to see how your Plant apps server handle the extra load caused by the Defrags or statistics updates. Tweak your script if the server cannot handle the load (Add more WaitFor statements or Waitfor a longer time).
Create a Maintenance plan
We are going to use the SSIS designer to create our maintenance plan. I will show you how to use Generic scripts to maintain the database.
Please note that on huge GBDB / Very busy servers you will need to use custom scripts that are doing "WAITFOR" to
Let the proficy server catchup the pending tasks as those maintenance plans can really slow down a server.
*This is a very basic example on how to setup a maintenance plan / sub plan. On a busy Proficy server, the best thing to do would be to generate multiple small scripts with pauses in between to accomplish those tasks to let the Proficy server catch up with the load.
Create your maintenance plan:
...
...
Add the update statistics sub plan:
Use the SSIS component toolbox and add an Update Statistics Action item.
...
Right-Click on the Update Statistics Task you just created and press Edit. Fill in the required
Configuration:
...
Click OK.
The next thing you must do is to configure the Maintenance plan Schedule:
...
Configure the schedule:
...
Your Maintenance Plan (Update statistics) is all set.Save it
...
| Anchor | ||||
|---|---|---|---|---|
|
SQL server log files are found in the Management section of the Object Browser of SQL.
...
You need to be a member of the security admin role in order to access these logs.
...
| Anchor | ||||
|---|---|---|---|---|
|
...
Plant Applications is installed with a Stored procedure that will determine whether there is Blocking occurring in the database. There may be a need to have heightened permissions to execute this. Below is an example of running it and observing that there are no blocks.
...
Real Time Information Portal Maintenance
...