205 reputation
16
bio website
location
age
visits member for 1 year, 8 months
seen Mar 4 at 17:23

Mar
4
comment Is it possible to get SQL Server 2008 to download a file from a URL
Thanks so much for this. I was beyond frustrated trying to get a larger value out of sp_OAGetProperty, never thought about this method.
Dec
9
awarded  Popular Question
Dec
4
awarded  Yearling
May
9
comment sleeping SPID blocking other transactions
It's actually not a compilation error in this case because the genius developer used INSERT INTO [table] SELECT * from [othertable] and it wasn't caught in peer. I ran the SPROC on Development 1000 times from ColdFusion in 3 simultaneous sessions and it never left open a transaction like it was on Production.
May
9
comment sleeping SPID blocking other transactions
I'm going to try SET XACT_ABORT ON and I will get back to you. Thanks so much.
May
9
comment sleeping SPID blocking other transactions
By the way, just checked ColdFusion DSN configuration and they have a CommandTimeout of 0s. I'm not sure if that makes it revert to 30s default, or infinite.
May
9
comment sleeping SPID blocking other transactions
Please read solution in my question post. I've marked this as the solution, because I think this is on the right track, but I still am confused. I have seen times when FusionReactor will report a long running query (over 30s) that completes fine, normally on older SPROCs before our standards or a large report that has an exception to that performance metric. I need to read and understand more about CommandTimeout but I just don't understand how it could be that. When I run the SPROC in SSMS, it was returning an error in less than 1s.
May
9
accepted sleeping SPID blocking other transactions
May
9
revised sleeping SPID blocking other transactions
added 934 characters in body
May
9
comment sleeping SPID blocking other transactions
I don't think we have a CommandTimeout specified. All our stored procedures are testing using sqlstress and must perform under 1000ms at 10 users 10 iterations (at the minimum). I'm still very confused about what has happened, but I'm updating the question with what we found was the problem.
May
9
comment sleeping SPID blocking other transactions
gbn: Thanks. I'm still confused. Our connections are set to never timeout (0). So you are saying if we re-use connections, and a connection runs a SPROC with an error (that has TRY/CATCH and TRAN blocks), it can somehow never run the ROLLBACK in the CATCH block thus locking up tables and keeping a transaction open? That doesn't make sense to me.
May
8
comment sleeping SPID blocking other transactions
All our SPROCs contain TRY/CATCH blocks and BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN statements, ROLLBACK being in the CATCH. Would XACT_ABORT still have an effect?
May
8
comment sleeping SPID blocking other transactions
I do notice that I have tons of sessions that say 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' and all of them are from ColdFusion (the main scripting used on our web servers). Perhaps ColdFusion when idle issues that statement to hold open a connection (as it's set to keep connections open).
May
8
comment sleeping SPID blocking other transactions
I assure you we use no adhoc queries from our web servers, and when I run that first query, even without the WHERE clause I only get the named SPROC on a handful of the SQL sessions, the rest that column is NULL.
May
7
comment sleeping SPID blocking other transactions
Thanks for DBCC OPENTRAN. It does tell me that the blocking PID has a open transaction, but no more details are available. sp_whoisactive returns the same information on the blocked process as I've been able to get on my own. There is still no details on what is going on other than 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED'
May
7
awarded  Commentator
May
7
comment sleeping SPID blocking other transactions
I've used all those and the last command is always what I put in my original post: SET TRANSACTION ISOLATION LEVEL READ COMMITTED. I also ran DBCC OPENTRAN and can see that there is an open transaction for the blocking PID.
May
7
comment sleeping SPID blocking other transactions
No, it's just the IP of one of our webservers... We have another idea to change every SQL login for each SPROC call during the login/registration process (which we feel is where the error is occuring) to a separate username which may help us isolate which SPROC could be causing the blocking.
May
7
asked sleeping SPID blocking other transactions
Apr
5
comment Turning on TF610 in SSIS
Thanks Aaron. Bad assumption on my part...