Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs.

Every SQL Server database has at least two operational system files: a data file and a log file. The data files contain both data and objects, such as tables, indexes, stored procedures, and views. The log files contain the information needed for retrieving all transactions in the database.

1) Database files

The SQL Server database files are:

Primary: Primary data files contain information for database initialization and point to other DB files. User data and objects can be stored either in these files or in secondary data files. Every database has a primary data file. The extension used for these files is .mdf.

Transaction Log: Transaction log files store the log information used to retrieve a database and maintain its integrity. There must be at least one log file for each database. The extension used for these files is .ldf.

For example, you can create a simple database containing a primary file with all data and objects, and a log file with all the information in the transaction log.

By default, all data and transaction logs are inserted in the same unit and path. This is done to control systems in a single disk. However, this is not the ideal for production environments. Since Microsoft SQL Server uses I/O calls from the Microsoft Windows operational system to execute reading a recording operations in disk (which manage when and how the I/O is executed), we recommend data and log files be inserted in separate disks.

Figure 1: Files in the same unit and path.


Figure 2: Files in different disks.

2) Changing SQL Server for a better performance with E3

Many E3 users and developers are not aware that the SQL Server database generated a log file with all the operations it performs. By default, 200 Mb are reserved for this log file, and it can be a rather small space when E3 performs too many operations in the database. Most of the times, once the changes below are implemented, its performance can enhance significantly.

To work around this behavior and increase the space reserved for transaction logs, follow these procedures:

• Via database manager (in this case, Microsoft SQL Server Management Studio Express), access the E3 database.
• Right-click it and select the Properties menu, as seen next.

Figure 3: Properties menu

• On Database Properties, select Options page and then change Recovery model property to Simple, as seen next.

Figure 4: Recovery model property.

• Still on Database Properties, select Files page and change the Autogrowth column of the row with the database’s name on it, plus suffix _log with a value larger than default. We suggest 2 Gb, as seen below.

Figure 5: Autogrowth column.

3) Changing an E3 application

Once it’s done, you can close the database generator and open the E3. In the application’s database, set up UseTransaction property as False, as seen below.

Figure 6: DB’s UseTransaction property.

Save the changes and then run the application.

NOTE: when UseTransaction property is enabled (True),
all operations in its scope are grouped. This improves the application’s performance, regardless of the database being used. However, its default value is False, for E3 versions compatibility purposes.