Skip to main content
Database Administrators

Return to Question

Added additional info
Source Link
Mohit Leekha
Mohit Leekha
  • 11
  • 3

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

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.

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

Post Migrated Here from stackoverflow.com (revisions)
Source Link
Mohit Leekha
Mohit Leekha

SQL job get blocked after some time by a process on master DB

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.