{"id":3315,"date":"2019-03-25T17:43:42","date_gmt":"2019-03-25T20:43:42","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/structure-query-language-sql-chapter-2-sql-server-databases-files-and-logs\/"},"modified":"2023-02-06T13:40:38","modified_gmt":"2023-02-06T16:40:38","slug":"structure-query-language-sql-chapter-2-sql-server-databases-files-and-logs","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-chapter-2-sql-server-databases-files-and-logs\/","title":{"rendered":"Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs."},"content":{"rendered":"
\n

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.<\/p>\n

1) Database files <\/b><\/p>\n

The SQL Server database files are:<\/p>\n

Primary<\/b>: 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<\/b>.<\/p>\n

Transaction Log<\/b>: 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<\/b>.<\/p>\n

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.<\/p>\n

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<\/b>.<\/p>\n

\"\"
\nFigure 1<\/b>: Files in the same unit and path.
\n<\/span>
\n\"\"
\nFigure 2<\/b>: Files in different disks. <\/span><\/div>\n

2) Changing SQL Server for a better performance with E3 <\/b><\/p>\n

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.<\/p>\n

To work around this behavior and increase the space reserved for transaction logs, follow these procedures:<\/p>\n