December 20, 2010 at 6:45 am
Hello
I've encountered a strange behavior with trying to KILL sessions!
I have an sp which is triggered periodically from an SQL Agent Job. The sp selects from the sysprocesses master table through which I get a list of sessions which have been inactive for an x amount of minutes. The sp will then KILL the sessions.
The SP (both calling it manually and through the SQL Agent) works fine, i.e. the session is killed (as indicated by the Activity Monitor), however when the application tries to query the database (whose connection has been terminated), the connection is automatiaclly re-established! On the other hand, if I manually kill the job from the activity monitor (right click on the session and selecting KILL process), the connection is terminated and the application will not be able to reconnect... this being the desired behavior.
Can someone please help me in identiying why this is happening? I'm using a seperate account for the SQL Agent (NTAUTHORITY) with permissions as sysadmin, while I'm setup as sysadmin when I connect to SSMS.
It seems that the KILL process is disconnecting the session but only on a temporarily basis?
Regards
Brian
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
December 20, 2010 at 7:49 am
Why are trying to kill off these sessions? This sounds like you are having problems with the connection pool?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2010 at 7:57 am
Sean Lange (12/20/2010)
Why are trying to kill off these sessions? This sounds like you are having problems with the connection pool?
Not really! One of our client requested this feature to limit the number of licences needed by the application. The app in question is MS Dynamics Navision 2009.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
December 20, 2010 at 8:04 am
Ugh! Navision aka No Vision. Just went through a lengthy migration away from that product for one of our companies. Interesting that their licensing is based on the number of sql connections. That seems to kind of conflict with the way connection pooling works. I wish I could help you with your issue. Good luck and hopefully somebody will have some insight better than mine. I am not sure I could offer much more than what you are already trying.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2010 at 6:46 am
hello
i wrote a similar constellation ( nav 4 sp3, sql-server2005 ).
it is the time if you kill a process forever or not forever,
under 10 min. ok, over 10 min. nav makes a reconnect !!!
December 21, 2010 at 6:52 am
Sean Lange (12/20/2010)
Ugh! Navision aka No Vision. Just went through a lengthy migration away from that product for one of our companies. Interesting that their licensing is based on the number of sql connections. That seems to kind of conflict with the way connection pooling works. I wish I could help you with your issue. Good luck and hopefully somebody will have some insight better than mine. I am not sure I could offer much more than what you are already trying.
Why did you choose to migrate away from that ERP?
December 21, 2010 at 6:53 am
g.graetz (12/21/2010)
helloi wrote a similar constellation ( nav 4 sp3, sql-server2005 ).
it is the time if you kill a process forever or not forever,
under 10 min. ok, over 10 min. nav makes a reconnect !!!
So how can you kill connections that are over 24 hours old and have been unactive for quite a while?
December 21, 2010 at 7:19 am
Ninja's_RGR'us (12/21/2010)
Sean Lange (12/20/2010)
Ugh! Navision aka No Vision. Just went through a lengthy migration away from that product for one of our companies. Interesting that their licensing is based on the number of sql connections. That seems to kind of conflict with the way connection pooling works. I wish I could help you with your issue. Good luck and hopefully somebody will have some insight better than mine. I am not sure I could offer much more than what you are already trying.Why did you choose to migrate away from that ERP?
It was a business decision to migrate that company to the same system as all of our other companies. Basically so we could consolidate the inventory and ordering processes into a single system. Makes things a lot easier for accounting that way too. Sharing inventory etc...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2010 at 7:22 am
Sean Lange (12/21/2010)
Ninja's_RGR'us (12/21/2010)
Sean Lange (12/20/2010)
Ugh! Navision aka No Vision. Just went through a lengthy migration away from that product for one of our companies. Interesting that their licensing is based on the number of sql connections. That seems to kind of conflict with the way connection pooling works. I wish I could help you with your issue. Good luck and hopefully somebody will have some insight better than mine. I am not sure I could offer much more than what you are already trying.Why did you choose to migrate away from that ERP?
It was a business decision to migrate that company to the same system as all of our other companies. Basically so we could consolidate the inventory and ordering processes into a single system. Makes things a lot easier for accounting that way too. Sharing inventory etc...
Ok thanks, we are considering implementation here. Any pointers about your experience and or better solutions out there?
December 21, 2010 at 7:29 am
Ok thanks, we are considering implementation here. Any pointers about your experience and or better solutions out there?
Well in all fairness, Navision is probably a better system than what we migrated too. 😉 We ported people back to our homegrown mainframe app that is 20+ years old now.
The only piece I really had to deal with was converting all the data from their system into ours. I had very little exposure to the user side of the system. The contracts and licensing were really expensive which was one of our primary deciding factors.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply