Solved

SQL Head Block not being released

Posted on 2010-08-13
1
1,406 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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now