Solved

Large Lock in sql2005

Posted on 2013-01-09
1
259 Views
Last Modified: 2013-01-14
I have one session_id exist since 3rd-Jan, It have hold large number of lock and this caused some performance degrade with our db, I just curious why it does not release the lock ? If I kill this session, it might take long time to rollback as the number of lock are extremely large.


Number-of-lock
0
Comment
Question by:motioneye
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38762029
usually this is due to "missing indexes", as a index will make sure locks are not on pages or full tables, but only on row level (if possible)
of course, this is only possible for OLTP transactions, which indeed work on 1 (or few) rows.
if you have large batches running, they will best run on full tables.

so, you need to check what the session (111 and 107) are doing ...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how many extra RAM for SQL server is needed 22 43
How to place a condition in a filter criteria in t-sql (#2)? 10 55
Help with simplifying SQL 6 54
Strange msg in the SSMS pane 13 58
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question