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.

Comments

[Coach handbags for sale]

Wow，great content and your blog template is so beautiful. Is this template free or not. If so, would you please share this template? if not, how much does it cost? Thanks a lot!

[srenders]

That might indeed happen when using the DBCC INDEXDEFRAG statement. In fact it's better to use the ALTER INDEXstatement with the option:
REORGANIZE

This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

[John Clark]

If running sql 2000 make sure you read this knowledge base article: 907250: "You may experience concurrency issues when you run the DBCC INDEXDEFRAG statement in SQL Server 2000"

This happened to us and locked our users out of the application until we stopped the defrag.