When you are working with a Microsoft Dynamics-NAV application and a Sql Server database, it's important to keep the following information in mind:
A Sql Server database consists of data file(s) and transaction log file(s). The data file(s) contain you data and the transaction log stores the details of all the modifications that are performed on the database and the details of the transaction(s) that performed the modification(s).
This feature of logging the details of transactions can not be turned of in Sql Server. This implies that your transaction log file(s) will keep growing while you are using your database. The way that these transaction log file(s) grow and the type of data stored can be configured. You can configure the transaction log file(s) to expand as needed.
When a transaction log file grows until the log file uses all available disk space and can not expand any more, you can no longer perform any data modification operations on your database.
To prevent the transacton log file(s) from growing unexpectedly, consider one of the following methods:
- Set the size of the log files to a large value to avoid the automatic expansion of the log file.
- Configure the automatic expansion of log fils by using memory units instead of a percentage.
- Change the recovery model. Based upon how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up: Simple recovery model | Full recovery model | Bulk logged recovery model
By using simple you can recover your database to the most recent backup of your database. By using Full or Bulk-Logged you can recover your database to the point of failure by restoring your database with the transaction log file(s) backup.
By default in Sql Server the recovery model is set to Full. Then you will need to regularly backup your transacion log files to keep them from becoming too big. You can change the recovery model to Simple if you do not want to use the transaction log files during a disaster recovery operation.
- Backup the transaction log file(s) regularly to delete the inactive transactions in the transaction log.
- Shedule the Update Statistics option to occur daily.
- When defragmenting indexes use DBCC INDEXDEFRAG instead of DBCC DBREINDEX. Using DBCC DBREINDEX, the transaction log file might expand drasticly when your database is in Full recovery model. And the DBCC INDEXDEFRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.