May 13, 2008 at 10:34 am
On a database on a SQL Server 2005 server we have some synonyms that are for tables in a database that is on a SQL Server 2000 server. The synonyms are used through the Great Plains application by a trigger that sets ansi settings and executes a stored procedure as a different user. The procedure executes and does inserts, updates or deletes using the synonyms; the trigger reverts back to the original user .
There are 20 tables that have these triggers that each execute a stored procedure.
On the SQL 2000 server the login/user that was used to execute the stored procedure, sometimes instead of doing the work and then disconnecting it stays connected to the database/server and goes dormant. It appears that this is causing issues on the server.
Does anyone know why this is happening or can tell me how to make sure the user disconnects after the work of the procedure is completed?
Thank you much for any insight you can give.
Ellen
May 14, 2008 at 6:37 am
I guess I would first ask why a dormant connection is causing an issue. They should not interfere with anything on the SQL Server end.
Moving on...
Dormant connections are usually the result of connection pooling. When a connection is closed by an application (in this case, your SQL 2005 server) if connection pooling is enabled the pool sets the connection to dormant and leaves it connected for some period of time (it may be configurable, but I cannot remember). After this period of time, the connection pool closes the connection and assumes that the overhead of leaving it open is not worth the cost of reconnecting.
So, check your connection pool settings. It could be that you have pooling enabled and you simply need to turn it off.
May 14, 2008 at 7:45 am
Thank you for the reply. You are correct, the dormant sessions should not be causing any issue. We are grasping at straws. Until we started using the synonyms the situation did not occur and at the same time we implemented this use our SQL Server 2000 server has experienced a far greater number of deadlocks and timeouts. I was looking at anything that was new/different.
I was surprised that the connection pooling was working from the server for these sessions. I had not taken into account that although there are multiple clients/users that connect to the 2005 database all of them switch to the same user when executing the procedures that connect to the 2000 server/database. I don't know if that would create a slow down for the individual users or not. What do you think? I do know that using connection pooling or not is configurable so I could turn it off. Typically for a single user/connection pooling is beneficial. In this case it is the same user by impersonation but multiple users in reality.
Again , thank you.
May 14, 2008 at 8:20 am
Connection pooling is somewhat global - look in the "ODBC Data Source Administrator" (yes, go here if you are using the OLEDB Driver) and check the settings on the "Connection Pooling" tab. It's actually related to the drivers.
Another thought is that is is not the dormant connections so much as DTC transactions mucking things up on you. Look carefully at the transactions that are open when you are having a problem.
November 8, 2010 at 7:02 am
Were you able to find a resolution to this? We are seeing something similar.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply