Blocking but not sure why
-
I have a lead blocker with a "sleeping' SPID and wait type of "NULL", so its appears it is not doing a commit.The spid is using table "EC_Commands".
The waiting spid is for a intent to shared lock but on a different table "archive_patients" both commands are reads and neither has any triggers.
Why is it causing blocking? I would get it if they were using the same table
THANKS
-
Maybe sp_whoisactive will give you more details to help you isolate the problem?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks, I the screen shot is not to good but its sp_whoisactive with leadblocker
-
that is probably just the most recent query that the blocking SPID ran, it may still be holding locks from a previous INSERT, UPDATE, or DELETE. You can query sys.dm_tran_locks to find out what locks are held:
SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.status AS session_status,
DB_NAME(l.resource_database_id) AS database_name,
OBJECT_SCHEMA_NAME(l.resource_associated_entity_id, l.resource_database_id) AS schema_name,
OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,
l.request_mode, l.request_type, l.request_status
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.resource_type = 'OBJECT'
AND s.session_id = ???
ORDER BY s.session_id, 6, 7, 8
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql -
Thanks Chris, That makes a little more CENTS$
I'll give a try as soon as it blocks again and report back
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply