SQL maintenance plan

garak
garak Member Posts: 3,263
edited 2006-09-04 in NAV/Navision Classic Client
Hy,

how looks your maintenance plan on sql server for navision.
I've activated:

Steps: (every Day at 04:00)
1. sp_defragdatabase DATABSE
2. sp_updatestats

They are more jobs i must activate :?:
What is with empty records in SIFT Tables :?:

Regards
Do you make it right, it works too!

Comments

  • garak
    garak Member Posts: 3,263
    garak wrote:
    Hy,

    how looks your maintenance plan on sql server for navision.
    I've activated:

    Steps: (every Day at 04:00)
    1. sp_defragdatabase DATABSE
    2. sp_updatestats

    They are more jobs i must activate :?:
    What is with empty records in SIFT Tables:?:

    Regards
    Do you make it right, it works too!
  • bbrown
    bbrown Member Posts: 3,268
    I prefer to script these at the table level, as not very table needs to have indexes and stats rebuilt daily.
    There are no bugs - only undocumented features.
  • nunomaia
    nunomaia Member Posts: 1,153
    1 - I’m guessing that DBCC DBREINDEX is equal to sp_defragdatabase.

    In quite large databases it’s almost impossible to rebuild indexes every day. In some tables it’s not need to rebuild every day the indexes.

    You can also shrink database if is needed

    2 - From Application Desogner's Guide:

    When you delete a record from a base table, the SIFT table is updated in the normal
    way and all of the aggregated totals are updated. However, the record is not deleted
    from the SIFT table; its corresponding totals in the SIFT table are set to zero. The
    entries in the SIFT table are not removed because there is a performance benefit to
    be gained for future updates by keeping them.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • DenSter
    DenSter Member Posts: 8,307
    Don't shrink the databas,e it will only expand again anyway. You should set the database files to a set volume and expend it as needed.

    To get rid of the zero entries, you need to do a table optimize from within navision. File/Database/Information, click the tables button, select your tables and hit the Optimize button. This rebuilds the records in the SIFT tables.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • ara3n
    ara3n Member Posts: 9,258
    Denster this also rebuilds the keys as well.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • nunomaia
    nunomaia Member Posts: 1,153
    The only problem it’s that you can’t create a script in sql server to automated the SHIFT table’s optimization. I always try to create sumindex field in tables that don’t have deleted records.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • bbrown
    bbrown Member Posts: 3,268
    It is possible to script the delete of zero SIFT records in SQL, but the scripts must be updated whenever SumIndex definitions are changed. This can be a maintenance headache if constant development is being done.
    There are no bugs - only undocumented features.
  • garak
    garak Member Posts: 3,263
    OK, you can click the optimize button. But i must update more then 50 databases. And i will not click the Button manuell (that consuming to many time).

    This must do an automatic script.

    All batches i will create on sql-Enterprisemanager as an job.

    And i'm not sure, which jobs i need.

    On every Database work 10 between 150 users. And i've only the time between 04:00am and 06:00am to do something. Before and after this time there are batches in navision (NAS) or the users work.

    And i will not disturb the users by her work (than they shout :cry: )

    So, i need all jobs for sql-enterprisemanag. to optimize the database.

    Regards.
    Do you make it right, it works too!
  • nunomaia
    nunomaia Member Posts: 1,153
    Updating statistics and index rebuild can be easy automated from SQL Server.
    The only problem exists in SIFT tables.

    When creating sumindexfields avoid tables that have many record deletion.

    If you want to use the Navision Client to optimize the SIFT tables, it can be automated using a software that makes Automation of key strokes.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • bbrown
    bbrown Member Posts: 3,268
    Is that 50 databases or 50 companies?

    Reindexing is an offline operation (SQL 2000) and your 2 hour window is somewhat small. One option is to reorganize indexes that have less fragmentation rather than rebuilding them. Reorganizing an index can be done online. A general rule is to reorganize if fragmentation is between 5% to 30% and reindex if greater than 30%.
    There are no bugs - only undocumented features.
  • garak
    garak Member Posts: 3,263
    50 databases with one between 7 companys. In future there are more databases. 2- 4 databASES are stored on an Server. So we have a lot of servers where i must implemet the maint. plan. And i will not use navsion with key strokes to optimize the client.

    Regards
    Do you make it right, it works too!
  • bbrown
    bbrown Member Posts: 3,268
    That must be a rather expensive software license (and maintenance cost)?
    There are no bugs - only undocumented features.
  • ara3n
    ara3n Member Posts: 9,258
    I had created such routine that can be run from navision. you create a table and fill in the records you want to optemize and run the form. It will then automate the keystrokes.

    http://www.mibuso.com/forum/viewtopic.php?t=10331&highlight=optemize
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n

Categories