Monday, September 19, 2011

Running Fixed Assets Depreciation causes Microsoft Dynamics GP to "hang"

I just completed a full upgrade of Microsoft Dynamics GP from version 9 to version 2010 R2 for a client and they were going through their first month-end closing in the upgraded system. 3 weeks ago, after the upgrade, they reported experiencing an issue running Fixed Assets depreciation from two laptop computers, where apparently, when running depreciation the system would hang. The only option to recover would be to terminate the Dynamics.exe process from Task Manager. Nonetheless, we did not pay much attention to this at the time since the process was completed successfully from another machine, just in time to close the month of August - more on this later.

The client called back on Thursday morning, letting me know they were ready to run Fixed Assets depreciation again, and this time I offered to be onsite to see the problem first hand. So effectively this past Friday morning I drove to their location and stood behind the Sr. Accountant to see the process in action and spot any possible issues while there. The accountant proceeded to log into the company database for which he would run the depreciation, entered his September cutoff date and clicked on the Depreciate button... as luck would have it with some support cases, nothing happened and the process completed successfully. Well, after some chuckles and the typical apologies from the client, I was back in my car on the way home.

Fixed Assets - Depreciation Process Information

Halfway through, I received an email saying that as soon as I left, they logged into another company and were able to reproduce the hung up issue.

Now, I began playing all the typical troubleshooting plots in my head... the problem happens only in one company, the problem can be reproduced by all users, the problem can be reproduced on all machines. Typically, when an issue is constrained to one company, it's related to some problem with the data or the way that company is configured. Not a bad proposition since I was only dealing with some 300 assets... but I am in my car, remember? So I offered the client to look at the issue when I was back in front of my computer, since I had discarded a user or workstation being the culprits.

Back at home I VPN'd into their system, then RDP'd to the SQL Server. I had the Sr. Accountant log into GP and start the depreciation process again. In troubleshooting the issue, I could see that the depreciation process was being correctly added to the Process Monitor and that the process showed Active, but it did not seem to complete.

Process Monitor

I also ran a SQL Profiler and noticed that the same set of T-SQL instructions would appear to be processed over and over at the database level. This told me the depreciation process was in an endless loop of some kind and something was preventing it from finishing.

SQL Profiler Trace

I then offered to run the process from the server with the 'sa' user and noticed that the depreciation was stopping on a particular asset ID (by clicking on the Progress button). This was now promising, because I now had a piece of data to look at.

Fixed Assets Progress window
I queried the Asset Master table and noticed that this particular asset had an acquisition cost of zero. In looking at the Asset Book, I noticed that the Cost Basis was USD $.01 (1 penny). Not sure why this grabbed my attention, but I asked the Sr. Accountant why had they set this asset up this way and he replied that they did it only to record the asset and keep track of its location, but that it had been fully depreciated in the past.

Asset General Information
Asset Book
He also added that the process was working fine in GP 9.0

So I figured I would try something by changing the Depreciation Method to "No Depreciation". After all, if the asset had an acquisition cost of zero and a Cost Basis of 1 penny, what was there to depreciate? I ran the following statement to change the Depreciation Method to "No Depreciation":

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Remove the lock for the book being depreciated
DELETE FROM FA40203;

-- Change the depreciation method
UPDATE FA00200 SET DEPRECIATIONMETHOD = 15 WHERE COSTBASIS <= .01;

I then asked the Sr. Accountant to re-run the process and this time it completed in less than 10 seconds and produced the reports he was expecting.

Since it was not enough to fix this issue, I went out to the Microsoft Dynamics GP Partner Online Technical forum and found a case where a partner reported having the same issue at her client's site. It seems Microsoft has identified and logged this as a problem report, but no concrete fix date has been given for it. So for now, the above query should do.

Also, you could end up with a cost basis of 1 penny at the end of the useful life of an asset, which would throw the system into an endless loop if you attempt to depreciate such assets once more. If you feel this is your case, the above script should also correct the problem.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

10 comments:

Anonymous said...

Running into the exact same issue after upgrading a client from GP9 to GP 2010. Thanks for the fix. At least we know it is not just a user issue.

Anonymous said...

Also ran into the very same issue and your post saved a lot of time and effort. Many thanks.

Anonymous said...

Thank you for posting this. Saved us a ton of time in troubleshooting!!

Anonymous said...

We just had the same problem and this post really helped. Thanks!

Night Writer said...

Hello
I have the same issue, and i am trying to investigate in the same sequence as you did only i don't have any asset that has cost basis < 0.1. any other suggestions ?

Mariano Gomez said...

@Night Writer,

Please follow the steps with SQL Server Profiler I show in the article to identify the asset that's causing the problem.

MG.-

Mariano Gomez said...

@Night Writer,

Please follow the steps with SQL Server Profiler I show in the article to identify the asset that's causing the problem.

MG.-

HCarson said...

We have GP 2013 and we are having a issue with the tax depreciation. It seems to just stick and the new version doesn't have a progress button any longer. Any suggestions how to find the asset that could be causing the issue?

Mariano Gomez said...

HCarson,

You can indeed capture a DEXSQL.LOG and a SQL Server Profiler trace as a starting point.

MG.-

Unknown said...

I've used this before and it made total sense. Now, I have some assets that look strange. They might be okay and I'm just not familiar with what GP shows. I have 81 assets that have no cost basis or 0. Meaning, the FA00200 table shows 0, the GP interface shows blank on most but blank on others. On 24 of them, the Fully Depreciated flag shows N, the asset says Retired, and there is a Net Book Value. Is this right? Should I set the Fully Depreciated Flag to Y on these or set the Depreciation Method to No Depreciation?

Thank you!