1

I have a job which performs following steps on high level

  1. Load Stage table
  2. Load Fact table
  3. Apply lots of rules on fact table, so lots of updates

This was working fine for more than a year but now it gets stuck after applying some of the rules. Every time gets stuck at a different rule in step 3

SP_WHOISACTIVE suggests that some other session starts blocking the update of data and my job is waiting for lock
enter image description here

When I looked up the blocking session, its a sleeping session initiated by SA on master. It seems like for some reason SQL decides to block this execution after some time.
enter image description here

I tried to kill this blocking session but receive error "Process ID X is not an active Process ID"

My sql server is hosted in Azure using elastic pool and i have already tried to increase the db storage and cores by 25% but no luck.

Same job also loads other fact tables from different regions and those are all still working fine. The only difference data for other regions is around half. But this job was also working fine for this region for more than a year with similar data.

EDIT

Output from sys.dm_exec_requests enter image description here

Improve this question
6
  • exec_sessions is meaningless to check, see sys.dm_exec_requests to see what kind of command it is etc
    – siggemannen
    Commented Jan 2 at 13:27
  • If it's sleeping then someone opened a transaction and forgot to commit or rollback.
    – Charlieface
    Commented Jan 2 at 13:36
  • What is the query of the blocking process?...does it read from your facts table?
    – J.D.
    Commented Jan 2 at 13:42
  • I have added the command, wait resource from sys.dm_exec_requests in the questions
    – Mohit Leekha
    Commented Jan 3 at 7:49
  • What does dbcc inputbuffer(35) say? You are leaving out way too much details in your screenshots. In the last picture, it appears like some index is created automatically, since spid 35 is not a user process, perhaps you have some setting that creates these indexes?
    – siggemannen
    Commented Jan 3 at 22:27

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.