On one of our testing environments, login attempts to a legacy web application that uses MS SQL Server were timing out and failing. I suspected that the reason might be that another process is locking one of the table needed in the login process.
I ran a query similar to this:
Which produces a result similar to:
It shows that an application is granted exclusive lock on the table EmailAddress, and another query is waiting for a shared lock to read from the table. But who is holding this lock? In my case, by checking the client_interface_name and program_name columns from the result we could identify that a long running VBScript import job was locking the table. I created a simple application that simulates a similar condition which you can check on Github. You can run the application and run the query to see the results.
It's a good practice to include "Application Name" property in your connection strings (as in the provided application source code) to make diagnosing this kind of errors easier.
I ran a query similar to this:
SELECT request_mode,
request_type,
request_status,
request_session_id,
resource_type,
resource_associated_entity_id,
CASE resource_associated_entity_id
WHEN 0 THEN ''
ELSE OBJECT_NAME(resource_associated_entity_id)
END AS Name,
host_name,
host_process_id,
client_interface_name,
program_name,
login_name
FROM sys.dm_tran_locks
JOIN sys.dm_exec_sessions
ON sys.dm_tran_locks.request_session_id = sys.dm_exec_sessions.session_id
WHERE resource_database_id = DB_ID('AdventureWorks2014')
Which produces a result similar to:
It shows that an application is granted exclusive lock on the table EmailAddress, and another query is waiting for a shared lock to read from the table. But who is holding this lock? In my case, by checking the client_interface_name and program_name columns from the result we could identify that a long running VBScript import job was locking the table. I created a simple application that simulates a similar condition which you can check on Github. You can run the application and run the query to see the results.
It's a good practice to include "Application Name" property in your connection strings (as in the provided application source code) to make diagnosing this kind of errors easier.
