Regular Maintenace

Good morning all,

New to Navision and trying to find out what is recommended, and also how to 
perform regular maintenance.

We are using Version AP 4.0 SP3 of Navision and SQL 2005.

From what I have read so far the following would be recommended.

I am assuming it does not matter which order I do these in??

1) SQL database backup (if I do this first and something goes wrong at least 
I have the backup);
2) Update statistics - can be done via SQL script (have the script as per 
Navision documentation.  And I could do different groups of tables on 
different days is that ok?
3) Rebuild Indexes I must admit I have no idea on how to do this.  Does 
anyone have any detailed notes???

As a start that seems to hopefully be it?

Regards

Dear James

About point 2:
"> 2) Update statistics - can be done via SQL script (have the script as
per 
> Navision documentation.  And I could do different groups of tables on 
> different days is that ok?"

This decision can be based on the size of your DB and lag of working time.
If you have weekend free from worker, you could to setup 2 Jobs or 
Maintenance Plan to perform the Periodic Activities:
- make BackUp
- Update the Statistics, Reorganize or Rebuild the Indexes (chooze 1 per 
action)

About point 3:
> 3) Rebuild Indexes I must admit I have no idea on how to do this.  Does 
> anyone have any detailed notes???

Reorganizing and Rebuilding Indexes for NAV is same as for another SQL-DB. 
It is very easy. Please, read the links:
- Reorganizing and Rebuilding Indexes - 
http://msdn2.microsoft.com/en-us/library/ms189858.aspx
- ALTER INDEX (Transact-SQL) - 
http://msdn2.microsoft.com/en-us/library/ms188388.aspx

"James Terrington" wrote:

> Good morning all,
> 
> New to Navision and trying to find out what is recommended, and also how to

> perform regular maintenance.
> 
> We are using Version AP 4.0 SP3 of Navision and SQL 2005.
> 
> From what I have read so far the following would be recommended.
> 
> I am assuming it does not matter which order I do these in??
> 
> 1) SQL database backup (if I do this first and something goes wrong at
least 
> I have the backup);
> 2) Update statistics - can be done via SQL script (have the script as per 
> Navision documentation.  And I could do different groups of tables on 
> different days is that ok?
> 3) Rebuild Indexes I must admit I have no idea on how to do this.  Does 
> anyone have any detailed notes???
> 
> As a start that seems to hopefully be it?
> 
> Regards
> 

About rebuilding and reorganizing indexes I have a stored procedure that 
could help you with this.

The stored procedure does index rebuild online or offline, index 
reorganization, statistics update, index reorganization and statistics update 
or nothing based on fragmentation level, LOB existence and index size. It is 
using the DMV sys.dm_db_index_physical_stats. 

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html 
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html 

Ola Hallengren 
http://ola.hallengren.com



"RedFox" wrote:

> Dear James
> 
> About point 2:
> "> 2) Update statistics - can be done via SQL script (have the
script as per 
> > Navision documentation.  And I could do different groups of tables on

> > different days is that ok?"
> 
> This decision can be based on the size of your DB and lag of working time.
> If you have weekend free from worker, you could to setup 2 Jobs or 
> Maintenance Plan to perform the Periodic Activities:
> - make BackUp
> - Update the Statistics, Reorganize or Rebuild the Indexes (chooze 1 per 
> action)
> 
> About point 3:
> > 3) Rebuild Indexes I must admit I have no idea on how to do this. 
Does 
> > anyone have any detailed notes???
> 
> Reorganizing and Rebuilding Indexes for NAV is same as for another SQL-DB.

> It is very easy. Please, read the links:
> - Reorganizing and Rebuilding Indexes - 
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx
> - ALTER INDEX (Transact-SQL) - 
> http://msdn2.microsoft.com/en-us/library/ms188388.aspx
> 
> "James Terrington" wrote:
> 
> > Good morning all,
> > 
> > New to Navision and trying to find out what is recommended, and also
how to 
> > perform regular maintenance.
> > 
> > We are using Version AP 4.0 SP3 of Navision and SQL 2005.
> > 
> > From what I have read so far the following would be recommended.
> > 
> > I am assuming it does not matter which order I do these in??
> > 
> > 1) SQL database backup (if I do this first and something goes wrong at
least 
> > I have the backup);
> > 2) Update statistics - can be done via SQL script (have the script as
per 
> > Navision documentation.  And I could do different groups of tables on

> > different days is that ok?
> > 3) Rebuild Indexes I must admit I have no idea on how to do this. 
Does 
> > anyone have any detailed notes???
> > 
> > As a start that seems to hopefully be it?
> > 
> > Regards
> >