Reduce the database size.

(1) Share

Report

Posted on by manish.yadav

1,893

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

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Saurav.Dhyani 14,380 Super User 2025 Season 2 on at

Like (4)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Alexander Ermakov 28,096 on at

Like (2)

Report

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

Was this reply helpful? Yes No
Suggested answer

Amol Salvi 18,698 on at

Like (1)

Report

Hi,

Look at the following link

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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
mmv 11,471 on at

Like (0)

Report

Hi,

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

Was this reply helpful? Yes No
Suggested answer

RockwithNav 8,600 Super User 2025 Season 2 on at

Like (0)

Report

Hey Manish,

Check out this thread if it helps you.

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

Was this reply helpful? Yes No
Ashwini Tripathi 4,624 on at

Like (0)

Report

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.

Was this reply helpful? Yes No