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, 2011 at 04:22 PM in Default

avatar image

84 8 8 12

(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, 2011 at 05:52 PM

avatar image

40.9k 39 94 168

(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 - but if you're using ASP Classic (VBScript) then it's pretty much up to you.

more ▼

answered May 14, 2011 at 12:46 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

I was misleading, I am using VB.net, my pages are aspx pages.

May 16, 2011 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, 2011 at 04:50 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: May 13, 2011 at 04:22 PM

Seen: 2938 times

Last Updated: May 13, 2011 at 04:22 PM

Copyright 2018 Redgate Software. Privacy Policy