Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV (Archived)

Reduce the database size.

(1) ShareShare
ReportReport
Posted on by 1,835

Hello All,

my navision 2013 is showing the very large data size...almost backup is 65GB. I want to reduce the size of the database. Is there any way to track the log files. and manually delete it for shorten the data size.

Thank and regards,

manish

*This post is locked for comments

  • Ashwini Tripathi Profile Picture
    Ashwini Tripathi 4,624 on at
    RE: Reduce the database size.

    When the AUTO_SHRINK database option has been set to ON, the Database Engine automatically shrinks databases that have free space. This option is set using the ALTER DATABASE statement. By default, it is set to OFF. The Database Engine periodically examines the space usage in each database. If a database has the AUTO_SHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.

    To decrease the size of a database, use DBCC SHRINKDATABASE.

    You can manually shrink a database or files within a database using the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement. If a DBCC SHRINKDATABASE or DBCC SHRINKFILE statement cannot reclaim all the specified space in a log file, the statement will issue an informational message that indicates what action you must perform to make more space eligible to be freed


    •A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.


    •Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.


    •A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.


    •Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

    You can find lots of materials explaining above options.

  • Suggested answer
    RockwithNav Profile Picture
    RockwithNav 6,562 on at
    RE: Reduce the database size.

    Hey Manish,

    Check out this thread if it helps you.

    forum.mibuso.com/.../how-do-you-shrink-a-database-whether-navision-or-sql

  • mmv Profile Picture
    mmv 11,465 on at
    RE: Reduce the database size.

    Hi,

    Please try to shrink the log file as well as maintain the SQL Maintenance plans.

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: Reduce the database size.

    Most of the time the reason for being the database size big is you don't have proper SQL Maintainence plan in place, you need to schedule regular backup of the database and transaction log, which will help to keep the transaction log small, check the what is the size of the primary database file and transaction log, take backup of the database and using the Shrink Option to shrink the log file.

    Check this link for maintenance plan

    msdn.microsoft.com/.../ms187658(v=sql.110).aspx

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Reduce the database size.

    You should schedule SQL Maintenance plan for transaction log backup on daily basis. This would ensure that, log file size is not increasing on daily basis and also would help you in recovering the data, if at all lost at any point in time.

    As soon as you take the log file backup, you can shrink the log file size. This would be only one time activity, as you have taken the Transaction Log Backup since long. 

  • Suggested answer
    Amol Salvi Profile Picture
    Amol Salvi 18,694 on at
  • Suggested answer
    Alexander Ermakov Profile Picture
    Alexander Ermakov 28,094 on at
    RE: Reduce the database size.

    You can reduce the database size by using SQL tools, e.g. shrink the database, there is plenty of material regarding that topic:

    msdn.microsoft.com/.../ms189035.aspx

    technet.microsoft.com/.../ms189080(v=sql.105).aspx

    There is also a good post:

    aboutsqlserver.com/.../size-does-matter-10-ways-to-reduce-the-database-size-and-improve-performance-in-sql-server

    Then, verify what are the settings for automatic database growth:

    www.simple-talk.com/.../sql-server-database-growth-and-autogrowth-settings

  • Suggested answer
    Saurav.Dhyani Profile Picture
    Saurav.Dhyani 17,965 Super User 2024 Season 2 on at
    RE: Reduce the database size.

    Hi,

    As per my understanding below is the best approach to do it -

    1. Take Backup of SQL Database (Complete).

    2. After backup change Recovery Model to Simple.

    3. Shrink the Log File.

    4. Change the Security Model to Full.

    With this the log file will be Shrink to minimum size and you have backup if anything went wrong.

    ** You can also create a SQL Job to automate above listed task daily in non working hours.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans