x

asp had sql error, now transaction is locked, how to locate/terminate transaction?

I ran my asp page and got this error: Execution Error: The statement has been terminated. The INSERT statement conflicted with the CHECK constraint "SSMA_CC$tblContracts$ModDate$validation_rule". The conflict occurred in database "GCTest05", table "dbo.tblContracts", column 'ModDate'. Now in SQL Server 2008 Express Mgmt Studio, I try to delete the offending Check constraint, but get "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)."
Then get referred to this website http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server⪻odVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1222 says Explanation Another transaction held a lock on a required resource longer than this query could wait for it.

User Action Perform the following tasks to alleviate the problem:

Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locks dynamic management views.

If the transaction is still holding the lock, terminate that transaction if appropriate.

Execute the query again.

...So how do I locate and then terminate the transaction? Do I need a ROLLBACK statement in each asp page if I get an error?
more ▼

asked May 13 '11 at 04:22 PM in Default

EuniceH gravatar image

EuniceH
83 8 8 9

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
I would run sp_who2 and see what sessions are currently blocking. If you have an open transaction that isn't being committed but is blocking you can obtain the SPID and this issue the command KILL SPID where SPID is the SPID number. Before killing the SPID you can run DBCC INPUTBUFFER (SPID NUMBER) to see what the session was doing. That way you can troubleshoot the root cause. I hope this helps.
more ▼

answered May 13 '11 at 05:52 PM

Tim gravatar image

Tim
35.5k 32 40 138

(comments are locked)
10|1200 characters needed characters left

To answer your second question:

Do I need a ROLLBACK statement in each asp page if I get an error?

Basically, yes you would, if your connection is being kept open. But the fact that your connection is being held open is probably a bigger problem. The simplest way to manage a transaction over a bunch of SQL statements from within C# or .NET is [to use TransactionScope][1] - but if you're using ASP Classic (VBScript) then it's pretty much up to you.

[1]: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
more ▼

answered May 14 '11 at 12:46 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

I was misleading, I am using VB.net, my pages are aspx pages.
May 16 '11 at 07:43 AM EuniceH
(comments are locked)
10|1200 characters needed characters left
Your asp is not the problem, the transaction that was in place wasnt committed. Thats why you got the error. If you are trying to drop the constraint from SSMS then you have to make sure nothing else is referencing it. If it is then that will cause a lock and the drop will not succeed. It is often best to carry out the drop with TSQL rather than the SSMS GUI to avoid this issue.
more ▼

answered May 13 '11 at 04:50 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x57
x55

asked: May 13 '11 at 04:22 PM

Seen: 2115 times

Last Updated: May 13 '11 at 04:22 PM