Management Reporter Services Causing the Terminal Server to Freeze "Like Clockwork"

(0) Share

Report

Posted on by Community Member

Hello:

Every Friday afternoon and typically sometime between 4:30 and 5:30 PDT, one of our Terminal Servers that hosts the Management Reporter (MR) 2012 CU13 Configuration Console and one of our instances of GP 2013 R2 experiences connectivity issues.

Specifically, during this time, users cannot access GP. The issue only happens for, at most, five minutes. Fortunately, users have not been typically posting, during this time. So, this issue has not caused any major problems.

But, it needs to be taken care of. Again, it's very odd that this happens "like clockwork" on this day of the week and timeframe.

I have considered moving Management Reporter to its own server. But, since this issue occurs at a consistent date and time, I'd like to see if there is something else that can eradicate the issue on this Terminal Server first.

We have both the Data Mart and Legacy Provider installed. But, we only use the Legacy Provider. Would removing the Data Mart eliminate this issue? If so, is removing the Data Mart simply a matter of disabling and removing the integration, stopping the two MR services, deleting the ManagementReporterDM database, and restarting the two MR services?

The standard Event Viewer messages that appear, when this issue happens, are as follows:

(1) The semaphore timeout period has expired,

(2) An error occurred while connecting to the database,

(3) A transport-level error has occurred when receiving results from the server,

(4) System.Data.EntityException: An error occurred while starting a transaction on the provider connection. See the inner exception for details. ---> System.Data.SqlClient.SqlException: SHUTDOWN is in progress. Login failed for user,

(5) The following unexpected error occurred while logging a map level exception: The underlying provider failed on Open, and

(6) Unable to log the following exception to the database:System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: SHUTDOWN is in progress.

Please let me know, if you have any thoughts on how this Friday afternoon issue can be eliminated.

Thanks!

John

*This post is locked for comments

I have the same question (0)

All responses (18)

Answers (0)

RogerRogerATX 1,515 on at

Like (0)

Report

>Specifically, during this time, users cannot access GP.

John, I think this is pointing at a network issue OR something on the SQL server during those times. I believe MR client does not use ODBC to communicate with the server app, it has its own transport, hence the communication port on the setup.

Whenever I've run into an issue with users not being able to post it's been a communication issue between the two servers (SQL and the TS) or SQL server being overwhelmed (the usual case)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the quick response, RogerRogerATX!

Can you recommend a way to counteract the issue?

John

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report
John -

A couple of questions:

Are both the Legacy and Datamart installed in the same SQL instance as your Dynamics GP databases?

How many GP databases do you have and what is the size of the largest one?

How much memory is installed on the SQL server? How much is allocated to SQL and how much is allocated to the Windows OS?

What other processes are occurring on the SQL server during the time this problem is occurring? For example: are backups being done? is a virus scanner running a virus scan on the server during this time?

Does this problem occur if just the Legacy Database or Datamart is used?

We have our MR datamart on it's own dedicated SQL server and the management console installed on an app server to keep separation. The Report Designer and Viewer are then installed on the terminal servers and we are experiencing any issues with MR.

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

Not quickly - I would search out the sp_WhoIsActive that someone wrote out there. I use it all the time because it gives me the length of the running query, the session id, the sql text being run, the login name, tempdb usage, wait info etc on one screen.

Then I would run it while the system is down to see if it's SQL causing it (do you have any jobs scheduled for that time?), and if it is SQL, you can then narrow it down to what is probably doing it.

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report

Here's the link to download sp_whoisactive.

Roger,

Could he also use sp_Who or sp_Who2?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sean:

The following are the answers to your questions:

(1) Yes.

(2) 20. 77GB.

(3) I would have to ask our DBA. But, from what I can recall, the amount of memory exceeds standard GP system requirements.

(4) I would need to ask our DBA.

(5) I would need to remove the Data Mart, in order to answer this. That's why I was wondering if I should do this and if my steps in doing so are correct.

Thanks!

John

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

>Roger,

Could he also use sp_Who or sp_Who2?

Probably -

John if you know the Data Mart SQL user ID that is updating the DM you can use sp_who {id}

or sp_Who2 to see which processes are running in SQL completely (not just the active queries)...

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report

As a comparison, we have 4 databases in a single SQL instance on our server. Our historical database is over 200GB in size, our production database is about 130GB, and our other two (including Dynamics system database) are relatively small. Initially, we had 32GB of memory installed (30GB of which was allocated to SQL, 2GB allocated to the windows OS) which exceeds the GP system requirements, but was causing issues for us with timeouts and processing times specific to PostMaster and running reports.

We recently increased our memory on the SQL box to 64GB with 48GB allocated to SQL and 16GB allocated to the Windows OS and moved our servers to flash (faster disks). While these improvements gave us more horsepower, we were still having issues. Come to find out our problem is our VEEAM backups. Now, while all this is great information, our issue was occurring overnight and not in the afternoon.

If you're not using the datamart, then I would stop the integration and remove the database. I'm not sure that's going to make a difference, because as Roger indicated, timeouts are usually networking or processing issues. I would encourage you to ask your DBA to find out what's taxing the system at the time this occurs to see if you can pin down the process. Can you recreate this at any other time?

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

If so, is removing the Data Mart simply a matter of disabling and removing the integration, stopping the two MR services, deleting the ManagementReporterDM database, and restarting the two MR services?

Yes, those are the steps.

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

>I'm not sure that's going to make a difference, because as Roger indicated, timeouts are usually networking or processing issues.

Yeah I've run into this on a regular basis when I've got MR updating the DM with multiple people hitting the same db with either an import or pulling a large smartlist.

We've got 96gb RAM on our SQL server, 64 allocated to SQL and it's not breaking a sweat anymore - we're running into the limitations of dexterity, the stored procedures underneath and of course - locked tables because the first two items take their time to run sometimes. (64 GP databases, 85 GB in our GP databases, 203+ GB in MR (the main db, not DM), and over 600 gb in tempdb on a 1 TB SSD - and i'm fighting for room lol)

Was this reply helpful? Yes No