?
Solved

SQL Head Block not being released

Posted on 2010-08-13
1
Medium Priority
?
1,468 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 1500 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

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
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

807 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