Solved

SQL Head Block not being released

Posted on 2010-08-13
1
1,424 Views
Last Modified: 2012-05-10
Experts,

First let me apologize if this has been asked before.

We have a database we are experiencing some major locking issues with.  We noticed some long wait times and a large number of waiting tasks.  Upon following the session IDs all the way back to the head blocking session ID that session ID is still marked as the head block, but has no SQL Command associated with it.  The only thing associated with the session is "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

Our web application is Cold Fusion Based hitting a SQL Server 2008 Server.

Please let me know where to look and what to look for.

Thanks in advance.
Chad
0
Comment
Question by:cpence
1 Comment
 
LVL 13

Accepted Solution

by:
dwkor earned 500 total points
ID: 33434924
In 99% of the cases concurrency and blocking issues related with non-optimized queries. Take a look at the sql statements system is executing and see is plans are not optimal (table/index scans instead of seek).

As the temporary solution you can enable read committed snapshot mode on the database (http://technet.microsoft.com/en-us/library/bb522682.aspx). In this mode writers do not block readers. It introduces extra load to tempdb so check that it's on the fast drive
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 service pack updates 5 42
SSIS GUID Variable 2 30
sql query 5 39
8 hour Continual coverage based on Time in and Time Out 9 17
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

685 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