As a NAV Developer, I frequently find myself working in the role “involuntary DBA” on behalf of customers wanting to know, “Why is my transaction log so large and is this really necessary?” NAV database log files often consume nearly as much or more space on disk than the volume of data in the database itself! In this post, I’ll try to provide an overview of how SQL Server works (in terms of physical files) and explain why transaction log files are probably properly sized even when your gut is telling you they’re much too large.
SQL Server databases contain two types of physical files: data and transaction log. For the purpose of this article, data files support our need to query accumulated data in a SQL Server database (think running a NAV Page or Report object). Log files support our need to insert, update, and delete data in the database (think add a customer, change a vendor’s phone number, post a sale order).
Logging and recovery concepts are implemented by all commercial database products in support of “ACID” (Atomicity, Consistency, Isolation, and Durability) requirements for transactional systems. Logging and recovery are not specific to SQL Server or NAV. Any operation that changes SQL Server data is recorded in the log file as part of a database transaction. Logging occurs in real-time as specific data is changed while a transaction typically spans numerous logging operations.
Picture the following NAV scenario: posting shipment and invoicing a sales order containing 50 lines. A user clicks Post and selects the Ship + Invoice option. SQL Server “Begins” a transaction. A Posted Sales Shipment Header record is inserted (logged). A Posted Sales Shipment Line is inserted (logged) for each line on the sales order. One or more Item Ledger Entries is inserted (logged) for each Sales Line. A Customer Ledger Entry is inserted (logged). One or more G/L Entry(s) are inserted (logged). Sales Lines are deleted (logged). Sales Header is deleted (logged). Assuming no errors occurred, SQL Server “Ends” (commits) the transaction. The transaction spanned several seconds and changes to data in numerous individual tables. Details of every data change were logged in real time as the posting operation progressed. Had an error occurred during posting the generated log file, detail associated with the transaction would have been used by SQL Server to “undo” any database changes associated with the transaction via a process called recovery or “rolling back”.
The example above demonstrates how a simple NAV operation may generate a significant volume of database changes. So what? The reality is that changes associated with an hour / day / week worth of posting activity represent only a small fraction of the overall volume of NAV data. Once again, why the need for such a large transaction log file? We indicated above that any operation resulting in data change is logged. NAV operations are not the only operations (nor should they be!) affecting data in your SQL Server database. In fact, the sum of database change as a result of NAV operation is probably not the most significant source of “change” within your database!
Imagine the following: the phone book for a major city is published and distributed. The phone book is really just a database of phone numbers and contact information indexed by last name, first name, and address as of some moment in time. It probably contains an entire page (or more) of numbers associated with last name Smith. Over the course of a year, several of the Smith entries likely move out of, and other new Smith entries move into, the city. The phone company doesn’t mail us updated pages for the phone directory with every change in residency during the year; rather, the changes are tracked (logged) and the phone book updated / republished annually. Were we to compare the 2015 version of a major city’s phone book to the 2014 version we’d likely find the Smith section spanning different page numbers with entries more accurately reflecting the actual Smiths residing in the community. The same concept occurs in many NAV tables with much higher frequency! Sales Header and Sales Line tables can be thought of as “phone books” of order data indexed by Document Type (Quote, Order, Invoice, Credit Memo, etc.) and Document No. New entries are frequently created. Older entries frequently deleted (via posting). Over time, the section of the Sales Header “phone book” originally containing all of the order entries no longer does. Entries existing at the time the “phone book” was published have been deleted (shipped and invoiced). New entries have been appended to the last page (and mixed in with new Quote entries that have also been added). When a user runs the Sales Order list page, SQL Server must now scan many more pages throughout the data file to present the complete list of Sales Orders in the system. Performance suffers.
SQL Server fortunately provides Rebuild and Reorg. Index tools that make the “update and republish the phone book” process simple and easy. Rebuild and Reorg. Index help to maintain a high performance SQL Server environment but both tools do result in extensive use of the log file – rebuilding or reorganizing table data is logged the same as any other data change. When an index is rebuilt, SQL Server is effectively moving every field and record comprising a table from its current location to a new location in a SQL data file. When all indexes in a database are rebuilt/reorganized as part of a database optimization job, it now becomes easier to understand why the correlation between log file size and volume of data in the database. There’s a good chance virtually every field, record, table, and index structure are moving from an existing data file location to a new location within the file (and logged along the way).
But I really, really, really don’t want to tie up that much disk space for a log file that is mostly empty most of the time. The good news is that it is definitely possible to manage Transaction Log file size! It does take effort and a little more “administration”. ABC frequently helps create the initial SQL Server backup and optimization jobs associated with a NAV environment. SQL Server Index Rebuild / Index Reorg. jobs mentioned above are part of the optimization jobs and typically scheduled to run once weekly during “off hours” for all indexes in the database. SQL Server does not require all indexes be rebuilt at the same time in one job! It is definitely possible to create multiple SQL Server optimization jobs where each job targets only a portion of the NAV database for optimization and is run on its own schedule, resulting in less overall logging for any one job - thereby reducing the need for a very large log file!