Dynamics GP – 10 tips when running a General Ledger Year End on a large SQL Database

Introduction

A few weeks ago I assisted with a General Ledger year end for a GP user with a very large number of journals and consequently a very large SQL database.

Usually a year end is a fairly straight forward routine, however as this user has such a large database, along with additional SQL bits and pieces for disaster recovery and bespoke reporting, it required a little more thought to ensure things went as smoothly as possible.

In this post I thought I’d share the steps I took to hopefully assist anyone else facing a similar situation in the future.

Here goes…..

1) Do the basics first.

By basics I mean following Microsoft’s general Year End prerequisites which can be found here.

I find the most important steps are ensuring you have a good backup and also ensuring the general ledger codes have the correct posting type as this can be a pain to correct post year end.

2) Ensure you have enough disk space for the SQL Data and SQL Log files.

This usually isn’t a consideration when running the Year End however given the size of the data its something that needs planning when dealing with GL tables in the 100’s of GBs.

Microsoft’s official guidance on disk space is included in the Year End documentation that I’ve linked to above however I’ve copied the section on disk space below:

During the year-end closing routine, all the records that will be moved are put in a temporary table before they are moved to the GL30000 table. You must have free disk space that is equal to the size of the GL20000 table to perform the routine.

To find the current size of the GL20000 you can use the sp_spaceused system stored procedure i.e.

However I’d also add that when dealing with large data volumes you also need to plan for extraordinary SQL transaction log growth as well.

For example the SQL transaction log grew to 240GB when I did this particular year end. This is because various statements including the INSERT statements into GL30000 are completed in one SQL transaction. Therefore no matter what the SQL recovery model is of your database, the log file is going to grow while the process completes. This is standard behaviour so SQL can roll the whole transaction back should any issue occur.

3) Remove any Database Mirroring or Availability Groups.

In my scenario the user has database mirroring for disaster recovery. I could have left this on however this would result in a lot of transaction log being sent over the wire while the Year End ran potentially doubling the length time it takes. I always see the amount of time something takes, as the size of the window of opportunity for something to go wrong. The quicker I could get the Year End process to go, the less chance I had of something going wrong. On the down side it does mean I had additional configuration to complete post year end, but I was fine with this.

4) Remove any custom indexes

If you have any additional custom indexes on GL20000 and GL30000 its best to remove them. Again its not a problem if you don’t remove them, however it can slow things down and also increase the amount of transaction log that SQL will produce. This is because if you are DELETEing and INSERTing data into tables the indexes also have to be updated. Therefore the less indexes you have the better. This requires additional space and also additional log to record the operations.

**Don’t remove any of the standard indexes though.

5) Stop the SQL Agent.

Maintenance tasks like reindex routines sometimes use the SQL agent to run. As I suspected the Year End will run for a very long time, I stopped the SQL agent to prevent this from happening. Its always best to check that critical operations won’t be affected by this though. If so just disable any SQL jobs that run maintenance on the Dynamics databases.

6) Temporarily revoke permissions for any Reporting Logins.

Ideally I’d like to set the database to single user mode while the Year End ran however as I wanted to monitor the process I didn’t want to restrict this too much. I therefore disable logins that I knew were used for reporting in case anyone sets off a large report in the middle of the year end.

7) Ensure SQL log growth on the database is set sensibly.

As I mentioned above the SQL transaction log is going to grow during this operation regardless of the recovery model. Therefore go to the properties of the SQL database and ensure the growth is set correctly, and its set in MB’s rather than a percent. I set it to grow 10GB increments for this particular year end process.

8) Use SQL monitoring scripts

I like to nosy under the hood when running most things but I especially want a sneak peak on the General Ledger Year End. My tool of choice for this is Adam Machanic’s awesome free monitoring tool sp_whoisative. This is a free script you can use to monitor activity in SQL. Using this I can pretty much see at what point the Year End was up to. i.e. INSERTing the data to GL30000 etc.

**If you didn’t know about this free script, and this is all you take away from this blog I’d be happy :). Its a really cool free tool you can use when troubleshooting all sorts of things in SQL i.e. blocking etc.

I also want to monitor my database and log growth as the process is running. If I monitor this I can predict when the log will run out of space and need to grow. I then check the disk space to ensure the growth will happen smoothly. To do this I use this script

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

9) Be patient 🙂

One of the most important things is to be patient as this routine can last hours on a large data set. Its also important to never cancel the operation by manually closing GP down even if it says “Not Responding”. If it doubt you can run the sp_whoisactive stored procedure and check what’s happening on the server.

10) Once complete

After a huge sigh of relief I performed an additional backup and then started recreating the indexes, setting up the mirroring and restarting the SQL agent. I also shrank the SQL transaction log back down to a reasonable size.

Conclusion

I hope you find the information in this post useful. Although you may not need to follow all the points I outlined, I think the most important are disk space for the SQL transaction log, monitoring and lots of patience 🙂

Thanks for reading!

Dynamics 365 Business Central – Dimension set to “No Code” on GL Code but a value still posts for a exchange gain\loss adjustment

I came across an interesting situation today that I thought was worth documenting to see if there’s any known reason for this behaviour or if I’ve misunderstood something.

I’m unsure if this is a software issue, or by design, however I noticed that even though I’d set the dimension value posting to “No Code” on a GL code, a value was still posting for the dimension in a certain scenario.

The scenario is outlined below but in short the G/L entries affected were exchange gain and loss postings that were automatically created after applying a foreign currency invoice to a foreign currency payment.

The G/L account in question is the exchange gains\losses account. As you see I’ve specified this shouldn’t have any analysis to the “CUSTOMERGROUP” dimension code.

I then posted an Invoice and Payment at different exchange rates and analysed both to the Dimension Code “CUSTOMERGROUP”. (I used different value codes for each transaction as part of the test)

Then I applied the payment to the invoice to force an exchange gain\loss postings as per below.

When posting the application I expected an error however to my surprise this worked, and when I check the G/L entries the GL code 31900 has dimension code analysis posted to “CUSTOMERGROUP”. (its taken from the Payment as I used the dimension value code “MEDIUM” on the payment)

To confirm I’d set this up correctly I tried posting a standard journal to the GL code and I correctly received the error below:

While I appreciate its unlikely the system would ever be setup like this its interesting that the rule of “No Code” appears to have been bypassed in this particular scenario.

It would be interesting to know if there’s any reason for this?

Thanks for reading!

Dynamics GP Vs Dynamics 365 Business Central – Reconciling AP and AR to the General Ledger

Introduction

Its good practice to periodically reconcile the General Ledger control accounts to the sub ledgers. In this post I’ll highlight the reports and enquiries that can be used in Dynamics GP versus the reports and enquiries that can be used in Dynamics 365 Business Central to achieve this task.

Dynamics GP

On the General ledger side you can obtain a balance on a GL code at any given period via the “Enquiry > Financial > Summary” window shown below:

This window provides information on the debits, credits, net change and period balances for an individual account for the year selected.

I love this view as it gives you a lot of information in a handy window that’s also easy to consume. You can drill down by clicking on the blue hyperlink headings to get to the detailed information.

** Incidentally Dynamics GP stores the information displayed in the summary window in a separate table from the detail information. Its not summed data based on detailed transactions. This provides performance efficiencies if there’s lots of data however on rare occasions it might not accurately reflect the period balance. (seasoned GP users will be well aware of this headache).

Unfortunately you can’t get the General Ledger balance at a date that’s in the middle of the period using this window. If you want a General Ledger position at a given date I’d recommend using the “Reports > Financial > Trial Balance > Summary” report.

With the restrictions I’ve added above I can see the General Ledger balance at the 25/04/27. If I wanted a detailed breakdown I’d use the “Reports > Financial > Trial Balance > Detail” using similar restrictions.

Once you have the control account balance you can obtain the AP or AR balance via the relevant Historical Aged Trial Balance report. You can access this via “Reports > Sales (or Purchasing) > Trial Balance > Historical Aged Trial Balance”. The options for the report are shown below:

Key fields on this window are:

Print/Age as of:- Using this report you get a retrospective position on the ledger at any given time. For example if I wanted to reconcile back to January 2020, I’d simply change this date to 31/01/2020 and the report would disregard all activity after this point.

Select Transactions for Report Using:- You can select either Document Date or Posting Date. When reconciling to the General Ledger its recommended to use Posting Date.

Detail:- You can either tick or untick this option if you want a summary per Debtor or Creditor or the detail information.

Using the information from this report you can reconcile back to the GL to ensure the ledgers balance.

Its also worth noting that there is a “Reconcile to GL” feature you can use via “Tools > Routines > Financial > Reconcile to GL”. This tool is very useful as it provides an insight into potential differences between the GL and a given subledger. i.e. it would highlight manual journals that could be causing issues.

Dynamics 365 Business Central

If I want to see a quick balance of a General Ledger code in Dynamics 365 Business Central I’d start by going to the Chart of Accounts page as per below:

In this page I can filter for the General Ledger codes in question and filter the totals using the “Filter totals by:” option.

When I enter a “Filter totals by:” restriction the values shown in the “Balance at Date” and “Net Change” columns change automatically based on the filter (however the “Balance” column always shows the current balance). Therefore in the screen shot above the “Net Change” is showing the change in April and the “Balance at Date” is showing the balance at the end of April.

This is a great feature as I can simply change this filter to any date in the year. I can then drill down on the detail transactions by clicking the amounts.

** Incidentally, the “Balance at Date” and “Net Change” values are dynamic as the data displayed on the page is based on detailed data. (they also don’t exist as fields in a table – they are a special type of field called flow fields). The “Filter list by:” filter is a special filter called a “Flow Filter” that work with those flow fields and performance is maintained by SIFT indexes (SQL Indexed Views).

Its also worth noting I can get a simpler view showing all period amounts for a given General Ledger code by highlighting the General Ledger code and selecting “Navigate > Balance > G/L Account Balance” from the navigation bar.

Again I can drill down on the amounts to see detailed information. This also offers a more familiar way of viewing the balance for a Dynamics GP user.

To obtain a balance on the AP and AR side I’d use the relevant Aged Accounts reports as shown below. (just search for “Aged Accounts” to see these)

The key fields are

Aged as of:- this is similar to the GP option “Age/As of” and will give you the position “as at” a given date enabling you to reconcile to a point in time.

Print Details:- With this option checked the report is printed with transaction detail however left unchecked you are presented with a summary per debtor.

Using the information from this report you can reconcile back to the General Ledger to ensure the ledgers balance

Conclusion

As expected both products offer great and simple options for reconciling your sub ledger back to the General Ledger control accounts.

As I stated above I really like the GP summary enquiry screen however I also love the flexibility that the flow filters provide in Dynamics 365 Business Central. (FYI, this functionality is offered throughout the system).

Also, in Business Central, as the summary values are provided based on detailed data in the ledger entries table, I can guarantee the balances are providing accurate information every time.

Thanks for reading!