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
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.
Hey Manish,
Check out this thread if it helps you.
forum.mibuso.com/.../how-do-you-shrink-a-database-whether-navision-or-sql
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
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.
Hi,
Look at the following link
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:
Then, verify what are the settings for automatic database growth:
www.simple-talk.com/.../sql-server-database-growth-and-autogrowth-settings
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.
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
