Dynamics GP - Weekly / Monthly Maintenance Procedures

Maintenance should be run on a Weekly / Monthly Basis, or as soon as possible after a power failure, or loss of connection to the server for any reason.

1.       Log into SQL Server Management Studio. Open Query Analyzer

a.     These only need to be done in the event of a power failure, or errors concerning damaged tables.

b.    DBCC CHECKDB (‘MASTER’)

c.     DBCC CHECKDB (‘MODEL’)

d.    DBCC CHECKDB (‘DYNAMICS’)

e.     DBCC CHECKDB (‘<database name>’)

f.      Run each line separately to perform maintenance on the tables of each company to fix any minor structural errors in the database

g.    Repeat for each database

DO NOT run this script if users are doing transactions or posting in ANY company

2.       To ensure all users are out of the system, you can run the following script from the SQL Server Management Studio->Query Analyzer before logging in

1.    DELETE FROM [DYNAMICS].[dbo].[ACTIVITY]

2.    DELETE FROM [DYNAMICS].[dbo].[SY00800]

3.    DELETE FROM [DYNAMICS].[dbo].[SY00801]

4.    DELETE FROM [tempdb].[dbo].[DEX_LOCK]

5.    DELETE FROM [tempdb].[dbo].[DEX_SESSION]

a.     Run this script if you experience errors concerning locked records, or batches or records in use, or errors saying other users are logged in

b.    DO NOT run this script if users are doing transactions or posting in ANY company

3.       Before running any maintenance, ensure all users are logged out, and you are logged in as SA

a.     Check by going to Tools->Utilities->System->User Activity

b.    Ensure that sa is the only user present

4.       GP->Maintenance->Sql

a.     This process optimizes the databases to improve database performance.

b.    Company may become sluggish or slow if not run regularly

c.     Select Company

d.    Tick Recompile and Update Statistics

e.     Click Process

f.      Repeat for each active company including dynamics

g.    Usually only takes a few minutes

5.       GP->Maintenance->Check Links

a.     Select Series

b.    Click “All” to add all categories

c.     Repeat this for all series

d.    Most important series are Financial, Sales, Purchasing, Inventory, Company, System

e.     Click “OK”

f.      This process can take a long time, you may want to leave it overnight as long as it doesn’t clash with the backup schedule.

g.    Print and review all reports to understand the impact of the check links

6.       GP->Tools->Utilities->Sales->Reconcile

a.     You may select other options or leave the defaults to do all records

b.    Will fix minor errors in SOP

7.       GP->Tools->Utilities->Purchasing->Reconcile

a.     You may select other options or leave the defaults to do all records

8.       GP->Tools->Utilities->Inventory->Reconcile

a.     You may select other options or leave the defaults to do all records

b.    Will fix errors with allocated inventory

9.       GP->Tools->Utilities->Financial->Reconcile

a.     Select  options. Year is easiest.

10.   Monthly / Weekly Smartlists

a.     It is a good practice to keep excel exports of customer , vendor and inventory balances, as well as open transactions as reference on a monthly basis

11.   Monthly / Weekly Reports

a.     List all reports that need to be printed and archived in excel or pdf on a fixed schedule.

b.    Name of person/position who is responsible for running the report

c.     Name of person/position it should be delivered to

12.   Physical Backup

a.     A Backup Schedule should be set up in SQL to do a full backup of all databases as often as required

b.    Daily backups with a two week rotation meet most company’s needs

c.     To set up a backup schedule, connect to:

i.                SQL Server Management Studio

ii.              Expand Management

iii.            Right Click Maintenance Plans

iv.            Start Maintenance Plan Wizard

1.       Choose backup or maintenance tasks

2.       Choose which databases they will be performed on

3.       Schedule those tasks

d.    File backups  should be done on all shared folders containing all dictionary files, and any customizations or additional reports

e.     File backups should also be done on the Program Files>Microsoft Dynamics GP folders on the server and on each user’s workstation

i.                Backup of the Program Files>Microsoft Dynamics GP folders on each user’s workstation is only necessary if users have customizations specific to their workstation

f.      Ensure a physical backup is completed after maintenance

g.    Restore this backup to your test company to ensure it is working

h.    Log into SQL Server Management Studio

ii.              Right click on TEST database

iii.            Tasks>Restore>Database

iv.            Tick from device, choose your backup file to restore

v.              Tick Restore on the file

vi.            Click options on the left bar under “Select a Page”

vii.          Tick “overwrite the existing database”

viii.        Under the “Restore As” paths, ensure that they are pointing to the correct path and filenames of the database files you wish to overwrite (Eg. If you are restoring LIVE into TEST, ensure the path and filenames for pointing to TESTDAT.mdf and TESTLOG.ldf)

13.   Business Intelligence Backups

a.     Export copies of all customized reports in SSRS as rdl files

b.    Backup Analysis Cubes

c.     Backup any other customized reports done in any third party software

14.   Standard server maintenance

a.     Scan disk

b.    Defragment

c.     Diagnostics