December 29, 2010 at 6:43 am
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?
December 29, 2010 at 7:01 am
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
December 29, 2010 at 8:14 am
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
December 29, 2010 at 10:08 am
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?
December 29, 2010 at 10:13 am
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
December 29, 2010 at 5:47 pm
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
December 30, 2010 at 12:33 am
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?
December 30, 2010 at 2:05 am
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
December 30, 2010 at 5:58 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply