TSQL: What is causing orphaned table locks

Question

4.00/5 (2 votes)

See more:

SQL-Server-2005

Hi,

For the last three days I have been having to kill of processes to released table locks that cause my web app to throw time-out errors. There have been no changes to the system for many weeks apart from when I attempted to re-index the tables. I am sure that the tables were locked before that but I cannot confirm.

It is always the same tables (packages and event_log)
Transaction ID Transaction Name State Transaction Type Start Time Isolation Level # Locks # Page Locks # Row Locks # Other Locks Session ID Login Name
#Error implicit_transaction Active Full Transaction 09/10/2012 22:40:32 Snapshot 22 13 7 2 59 carma
Database Name Transaction State First Update Time # Locks # Page Locks # Row Locks # Other Locks
notxdb Active 09/10/2012 22:40:32 22 13 7 2
Resource Name
carma.event_log
Lock Type Request Mode # Locks Granted # Locks Waiting
OBJECT Intent Exclusive 1 0
PAGE Intent Exclusive 10 0
KEY Exclusive 5 0
carma.package
Lock Type Request Mode # Locks Granted # Locks Waiting
OBJECT Intent Exclusive 1 0
PAGE Intent Exclusive 3 0
KEY Exclusive 2 0
Currently Executing SQL Statement
--

Sorry but I can't remember the exact query. None of the queries are complex but I can't event find out what type of query it was until it reoccurs, and I'd like to avoid that if possible >_<<br mode="hold" />
Ideally I'd like to avoid any downtime at all but if I'm pretty sure the solution will help than I can schedule some downtime within a couple of hours

Does anyone have any idea what is causing this or how I can find out what causes it?

Thanks
^_^

Posted 9-Oct-12 22:31pm

Greysontyrus

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

skydger · Accepted Answer · 2012-10-09T23:33:00

Hi! I suggest you to run SQL Server Profiler to gather full informaion about what's going
on with the database and a detailed information about locks (Please refer to this link for details: http://msdn.microsoft.com/en-us/library/ms173789(v=sql.105).aspx[^]).
Then try to use Database Engine Tuning Advisor to solve these issues.

upd.

In sys.sysprocesses we can see processes information, but we need a process or processes which are getting blocked

SQL

select * from sys.sysprocesses where  spid >= 50 and blocked <> 0

also you may need to use this query:

SQL

SELECT loginame, cpu, memusage, physical_io, spa.*
  FROM master..sysprocesses spa
 WHERE EXISTS( SELECT spb.*
                 FROM master..sysprocesses spb
                WHERE spb.blocked > 0 and
                      spb.blocked = spa.spid )
       AND NOT EXISTS ( SELECT spc.*
                          FROM master..sysprocesses spc
                         WHERE spc.blocked > 0 AND
                               spc.spid = spa.spid ) 
 ORDER BY spa.spid

This query will give you information about how many shared locks you have

SQL

SELECT l.resource_type, l.request_mode, l.request_status, l.request_session_id
  FROM sys.dm_tran_locks AS l
 WHERE resource_database_id = DB_ID();

If in won't help, try to search locks with solution from this article: http://www.techrepublic.com/blog/datacenter/find-blocking-processes-using-recursion-in-sql-server-2005/275[^]