Happy New Year. Wishing everyone a very successful 2014.
I have been back from leave for two weeks, but have been really busy with support cases.
One recent case highlighted a SQL Server setting that I have never used before and the negative consequences of changing the setting from its default value of "off".
The customer reported that when Payables Management transactions were fully applied and should be moved to history, that what was happening was the transaction was being removed from the Open tables, but was failing to be added to the History tables. The end result was that the transactions would just disappear from the system.
We used the Support Debugging Tool (http://aka.ms/SDT) to capture logs. From the Dexterity side nothing looked unusual, but from the DEXSQL.LOG the system showed a couple of unusual behaviours.
Reviewing the case notes, I found that it was mentioned in passing that the problem started occurring AFTER the Microsoft Dynamics GP system was migrated to a new server.
So we needed to look at the Database configuration and Server Configuration to see what differences could be responsible for this behaviour
Further investigation using the sp_helpdb command did highlight that the Dynamics GP system and company databases were set to compatibility 900 (SQL Server 2005) and the database owner was not set to DYNSA. These settings were easily fixed using the Options page on the Database Properties to change the Compatibility Level and the T-SQL command sp_changedbowner 'DYNSA' to change the owner for each of the databases. While these settings were wrong, I doubt they were responsible for the issues we were seeing.
I then received an email which explained that the customer's IT Administrator had been comparing settings on the SQL Server between the old and new servers and noticed some differences on the Server Properties on the Connections page.
The differences notices was that on the old server: the Remote Query Timeout value was set to 1200 seconds instead of the default 600 seconds, and on the new server: the option xact abort was checked (the default value is unchecked).
The Remote Query Timeout being 1200 would not cause any problems for normal operation. I suggested they move that setting to the new server. There may have been a reason it was increased.
The xact abort setting however was once that I have never come across before, so I did some research to understand what it means and how it can affect the behaviour.
Looking at SQL Books online for the Server Properties Connection Page it explains the xact abort settings as "Rolls back a transaction if a Transact-SQL statement raises a run-time error."
I also found an interesting article about the option: Use Caution with Explicit Transactions in Stored Procedures.
While the blog article suggests that it would be safe to leave the option on, the author does not know the Dynamics GP application. Dynamics GP does generate a number of expected runtime errors (such as the column desSPRkmhBBCreh one) and ignores them or handles them as appropriate.
However, if the xact abort option is enabled, it will cause transactions to be rolled back for the slightest error.... even ones that Dynamics GP expects.
After unchecking the option and restarting the SQL Server, the problems stopped occurring.
Hope you find this useful.
That's an interesting case as I recently had a concern on my GP 2010 server about performance issues with the Project Accounting module when editing the budget for an existing Project... the GP client was like freezing for about several minutes for no reasons.. I looked on the system performance on the server side while this was happening and there we go ... the CPU was hitting nearly 100% for about 2-3 minutes without any reasons. The strange thing is that it doesn't happen to every existing project... sometimes it's just a new project I'm adding a Cost Category and when the CC is entered, at the moment I tab out of the field, the client freezes for several minutes. The next line sometimes is OK, sometimes freezes too...
This is really something I need to trace down thru SQL to understand what's going on ..
Have a great time,
Posting from John Lowther at Microsoft Dynamics GP DBA
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.