September 24, 2010 at 4:49 am
Hi,
Recently, i'm having a timeout problem in the execution of some stored procedures in a database.
I'm getting the following error:
"Timeout expired. The timeout period elapsed prior to completation of the operation or the server is not respondig".
The thing is that these stored procedures are fast and there is no reason to get timeout.
I've found that this error occurs when there is an oldest transaction that was not committed or rollbacked..When this error occurs, if i run 'dbcc opentran' i get the information of an existing oldest active transaction.
The transaction is started by a method in a c# .net apllication, and then several methods that uses several stored procedures in database are called, using the same trasaction. There are several modules of the application that uses this method. What i think that occurs is that sometimes one of this module does not commit the transaction and since that the apllication returns several timeouts in the following calls to the database and the application becomes very slow..
The only way i've found to solve this problem is restart the database server when this occur.. which is not good in a production environment.. :s
Anyone has any idea how to solve this?
Can i get the process that started the transaction to found the origin of problem?..
Any help will be appreciated! 🙂
September 24, 2010 at 10:17 am
Look into try-catch blocks in c# and make sure that you always commit or rollback transactions. Then you should use a finally block to release your connections back to the pool. It sounds like not only do you have some code starting and not ending transactions but you also are not properly disposing of your connection object which is why you are getting timeouts from other locations.
_______________________________________________________________
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/
September 24, 2010 at 12:38 pm
SeanLange, thanks for your reply. 🙂
I really was not disposing the connection, i was just closing it. :s
Regarding the try-catch blocks, i will make sure that all of them are properly commiting/rollbacking the transaction. (or at least try)
I'm been searching about these issues and i found that i could get the locked
objects(tables) in database by the oldest active transaction.
At least this will help me found the module that are not closing the transaction.. 🙂
Can i 'release' this objects or kill the transaction without restart the database?
Thanks again
September 24, 2010 at 12:47 pm
Well you can check in your finally block and if there any transactions either commit or rollback. Not the cleanest choice but disposing of your connection will force the .net garbage collector to rollback any uncommitted transactions. You might try looking into the "using" statement for .net also. This is only available in C# (at least the last time looked), there is no equivalent in vb.net. Any object wrapped in a using statement MUST inherit from iDisposable, like an ADO.NET sql connection object. It will basically convert code inside it to a try-finally block.
Check out the number of connections to your db in ssms. It will skyrocket up to 99 very quickly and if you keep running code it will timeout because it can't get a connection. If you do not properly dispose of your connection object it will totally hose your system. You might read up a bit on connection pooling and how it works with .net.
Hope my rambling helped. 😉
_______________________________________________________________
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/
September 24, 2010 at 12:51 pm
as-534320 (9/24/2010)
The transaction is started by a method in a c# .net apllication, and then several methods that uses several stored procedures in database are called, using the same trasaction. There are several modules of the application that uses this method. What i think that occurs is that sometimes one of this module does not commit the transaction and since that the apllication returns several timeouts in the following calls to the database and the application becomes very slow..
You're correct as to how this happens, and I've been at a few sites that have had this when I arrived. It requires a re-design. The only 'semi-safe' way to try to do this is to have the query calls at app layer with no timeout and to have it on the same physical box so that intermittant connection failure doesn't fail the transaction.
I've threatened DBA beatdown on developers who have attempted this. 🙂 It's a massive issue if anything goes wrong. The "right" way to do this is to remove all transaction control from external apps, and if something needs a transaction, build a wrapper proc that they'll call that will open and close the transaction, feeding in all necessary parameters for all the sub-proc calls.
This is the ONLY way I've found to avoid the issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 24, 2010 at 1:03 pm
...and to have it on the same physical box so that intermittant connection failure doesn't fail the transaction.
I don't know anyone that would even consider allowing an application on the sql box.
The "right" way to do this is to remove all transaction control from external apps...
Agree 1000%. There really shouldn't be any reason that the application layer is handling data requests complex enough to required transactions.
_______________________________________________________________
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/
September 24, 2010 at 1:28 pm
Sean,
I was really thinking in 'using' statement in c# 🙂 I'll read some on connection pooling.
Craig,
I had already thought about re-design the apllication, but it scares me a litlle.. :unsure:
besides she has grown a litlle bit latelly..
I'm wondering how do i create a method with all the necessary parameters and pass them to the web service method
that will take care of the transaction and all the necessary changes in database.
In one transaction i need to create an entry in a database and for that entry i need to create other entries in other
different tables, sometimes several times for each table..
I'm wondering how do i pass these variety of data (that needs to be serializable) to one single method that will truly take care of all changes..
Thanks a lot for your help!
September 24, 2010 at 1:34 pm
My suggestion would be to create a stored procedure that can handle all your inserts. Keeps it in a nice tidy package, makes transaction handling simple and allows you to change some business rules with nothing more than changing your stored procedure. On top of it all you get the clean separation between the business and data layers.
_______________________________________________________________
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/
September 24, 2010 at 2:40 pm
Sean: I've done ISS/SQL combined boxes before for dedicated apps/vendor apps before. It's not the optimal solution but it's doable if your userload isn't high. It's a special case scenario, for things like when you can't change the app... and it insists on opening transactions from the app. 🙂
as-53: We could probably help you streamline your proc calls into a single script if we could see them. You'd be using things like OUTPUT parameters to pass the results back up to the main script, SCOPE_IDENITTY() to grab the new ID created during the insert, etc.
It's quite doable, even at highly complex levels, just takes patience and a little knowledge of a few tricks that aren't obvious the first time you go through the process.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2010 at 4:51 am
Thanks for your help.
Since i already created the individual stored procedures for each step, which returns the 'scope_identity' value, i'm thinking to use transaction scope, like in this link (http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx).
Is it a reasonable approach?
Regards
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply