mibuso.com
Home› NAV/Navision Classic Client

User Menu Level

fazlehasanfazlehasan Member Posts: 70
edited 2015-03-12 in NAV/Navision Classic Client
We use NAV 5.0SP1 with SQL server 2005.

Recently we found that half of our database is occupied by
a single table User Menu Level (about 120GB!)

Searching the web I only could get this information that
it is used to keep the user's permission in the Menu Suite in the Object (BLOB) field.

Strangely it shows that it is less then 1GB in size when seen from NAV application side.
But when seen properties in SQL it shows about 122 GB!

Can someone throw some light on this -
(1) How can we reduce the size of the table since we are facing storage issues?
(2) If we delete records from the table will there be any problem - like the users
who have left the organization - can we delete those records?

Thanks in advance.
Any information on this would be very much appreciated.

Thanks.

Comments

  • winfywinfy Member Posts: 8
    edited 2011-11-17
    A normal size of the table 2000000061 "User Menu Level" is less than 1-2 MB.

    Some information about the table:

    There are two entries per user

    - User Restrictions
    - User Changes

    The first one "User Restrictions" belongs to the user's view of the Navigation Pane.
    The second one "User Changes" is the the Shortcut Menu of the user.

    If you delete the entry "User Restrictions" the worsed case is the the user see the complete Navigation Pane (no restrictions).
    If you delete the "User Changes" the Shortcut Menu is empty.

    You can add new Restrictions with the Navigation Pane Designer (Tools->Navigation Pane Designer).
    Every user can add the shortcuts themselves.

    To have a guess...
    When you add a user to NAV: Do you use a tool to copy the users-rights/roles/views ?
    Maybe something like that is the problem?

    regards,
    winfy
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Can you post some screenshots of the difference? I find it hard to believe that there is a difference since NAV also looks in SQL for the size of the object. The results should be the same.
    Marije Brummel | Freelance Business Central Specialist
  • fazlehasanfazlehasan Member Posts: 70
    Mark Brummel wrote:
    Can you post some screenshots of the difference? I find it hard to believe that there is a difference since NAV also looks in SQL for the size of the object. The results should be the same.
  • krikikriki Member, Moderator Posts: 9,112
    Mark Brummel wrote:
    Can you post some screenshots of the difference? I find it hard to believe that there is a difference since NAV also looks in SQL for the size of the object. The results should be the same.
    It is possible. I noticed that NAV doesn't count the space used by BLOB's and SQL does.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • winfywinfy Member Posts: 8
    kriki wrote:
    It is possible. I noticed that NAV doesn't count the space used by BLOB's and SQL does.

    Indeed, take a Look at Stryks Blog (Link)

    That's not your solution, but maybe the problem behind your problem?

    I still repeat my question: Do you (or other admins) use a tool to copy the users-rights/roles/views ?
    There are maybe some tools manipulate the BLOB in this table (Link).

    regards,
    winfy
  • fazlehasanfazlehasan Member Posts: 70
    We have deviced a solution for this problem -time to time running 'DBCC CLEANTABLE' on the table to keep the size in check.

    Thanks
  • MichaelEMichaelE Member Posts: 1
    Sorry for reviving this old thread, but this is a problem I've also encountered, and since this is the first result that appears in Google when searching for information on the issue, I thought I'd add a bit more info plus my own solution, just to help out any other people who arrive here while searching for the same issue.

    It seems that NAV can leave behind some "ghost data" in this table when modifications are made to the data in this table. I've seen an instance where the User Menu Level table size is over 20GB, but only contained ~450 rows with a total size of about 3MB (including BLOB data). Even after deleting every record from this table, 20GB worth of used pages still remained!

    I suspected that this might be due to SQL Server simply marking the records as being deleted but leaving the data in the pages (keeping them for rollback purposes, rather than physically removing them), but after running some scripts to analyse this, this doesn't seem to be the case.

    While the cause of the "ghost data" is still a mystery to me, I WAS able to fix it by copying all the data into a temp table, dropping and re-creating the User Menu Level table, then copying all the data back. Instead of dropping and re-creating the tabel, the "TRUNCATE TABLE" statement should also work, but I haven't tested it yet.
Sign In or Register to comment.