February 4, 2019 at 3:42 am
Sorry a bit new to how blocking works so forgive me if I sound stupid.
We have an agent job that runs at the weekend, over the last few weekends the job just continues to run and does not complete.
I checked what was blocking it, I could find a session from an application in awaiting command status.
I checked the query it had sent and it is a select statement and is using a function with links to a table that the agent job needs to update part of its process, however this was not actively running at the time so I cannot see why it would cause a lock.
The wait type was
| LCK_M_SCH_M Any ideas? |
February 4, 2019 at 4:41 am
That's a schema modification lock. Did you have any online index maintenance jobs running at the same time or anything like that?
February 4, 2019 at 5:12 am
You probably have a long running query.
Execute this to see what's running and how long it's been running for:SELECT sqltext.TEXT,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
February 4, 2019 at 5:46 am
Thank you for the responses.
We do have an index job but this runs at night and had completed by the looks of it.
In the end I killed the session I stated above and the agent job completed but I still don't understand why it was blocking when the select appeared to me as completed and in sleeping/awaiting command status.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply