Blocking in SQL Server 2008

Question

Blocking in SQL Server 2008

sporoy

Hall of Fame

Points: 3114
More actions
December 29, 2010 at 6:43 am

#231510

Hi, for a while I have a blocking situation with a database in a consolidated SQL Server 2008 EE SP1 failover cluster x64 environment. I see blocking by querying:
select blocking_session_id, * from sys.dm_exec_requests where blocking_session_id <> 0
The problem is when I query the blocking session_id with dmv there :
select * from sys.dm_exec_requests where session_id = 1476
select * from sys.dm_os_waiting_tasks where session_id = 1476
No results returning. How can a session with no task can block other INSERT's?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Started a transaction, took locks, didn't commit or roll back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

check sys.dm_exec_sessions to be sure you're seeing all connections. sys.dm_exec_requests only shows active connections.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

sporoy Hall of Fame Points: 3114 More actions · Answer 3

Yes I can see the locks with

select * from sys.dm_tran_locks

Also see tran at:

select * from sys.dm_tran_active_transactions

as implicit transaction

Is there any solution to blocking? Implicit Transaction seems open for 6 hours? Kill?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

First find out who the connection belongs to and what it's doing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184388 More actions · Answer 5

in addition to Gail's and Grant's advice I'd suggest getting Adam Machanic's sp_WhoIsActive which will also give you the query text.

You can get the query text yourself using sys.dm_exec_sql_text().

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sporoy Hall of Fame Points: 3114 More actions · Answer 6

But there are no running or waiting queries, so there should be no query running at the time, problem is transactions not committing for hours, probably due to a mishandling of application code. Only thing we can do is to make the application transaction handling better?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

sporoy (12/30/2010)
But there are no running or waiting queries, so there should be no query running at the time, problem is transactions not committing for hours, probably due to a mishandling of application code.

Thing is, if you kill that connection, everything it's done since the transaction started will be rolled back. That's why it's important to try and find out what it was doing so you have some idea of the impact.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

Jack Corbett (12/29/2010)
in addition to Gail's and Grant's advice I'd suggest getting Adam Machanic's sp_WhoIsActive which will also give you the query text.
You can get the query text yourself using sys.dm_exec_sql_text().

Absolutely. sp_WhoIsActive is excellent.

I just hate recommending it to people who are learning because then they won't use the DMOs and learn how they work so that they can do stuff that sp_WhoIsActive might not do or that they want to do differently, or whatever.

It's an amazing bit of code.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software