Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MS SQL Server 2000 - sp_cursorclose Issue

 
Rajesh MadhanGopal
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

We have an issue with Microsoft SQL Server 2000, we connect to MS SQL Server 2000 SP 4 from WAS 6 using Microsoft type 4 JDBC Driver and as transactions happen, there is a lock caused by sp_cursorclose which stays locked for close to 5 hours causing the whole database inaccessible.

The other database server is being locked by system stored procedure sp_cursorfetch which again gets locked for 5 hours and causes the whole database to be inaccessible. Even after releasing the connections from the application side, the issue still persists.

Is there any known solution to the problem?
Is there a known issue with these 2 stored procedures?

Right know we just kill the blocker processes to solve the issue.


Kindly let me know if there is a solution to the problem.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What process causes the block? Is it your client application? (check sp_who/sp_who2 to see).
 
Rajesh MadhanGopal
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is some part of the blocker LOG: Does this help making it clear?

----------------------------------------------------------------------------------------------------
today
------------------------------ --------------------
Blocked Transaction Reported: Jul 19 2007 5:50PM

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

-------- ----------- --------- ----------- --------- ----
Blocker: 78 Waittime: 0 Waittype: 0000

-------------
Inputbuffer:
EventType Parameters EventInfo
--------- ---------- ----------------
RPC Event 0 sp_cursorclose;1

(1 rows(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

-------- ----------- --------- ----------- --------- ----
Blockee: 72 Waittime: 9911281 Waittype: 0004

-------------
Inputbuffer:
EventType Parameters EventInfo
-------------- ---------- -------------------------------------------------------------------------------
Language Event 0 UPDATE FORM_TRACKING SET CurrentItamUserId = 'EP6154' WHERE TrackingId = 659115

(1 rows(s) affected)
[ July 23, 2007: Message edited by: Rajesh MadhanGopal ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What application is process 72? What's it doing when the block happens (other than just issuing the update statement)? Is it behaving otherwise normally?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic