0

We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker (A one in the header blocker column). This seems to be blocking other processes in different databases. I can see their ids in the blocked by column when I select one from the drop down. Am I correct that it is cross database blocking? I didn't think this was possible. They are all running exactly the same stored procedure, but they have their own instance in each database. They are doing updates and inserts, but only within their own databases.

eg

UPDATE  SCA
SET SCA.date_last_read_only = TDR.date_seen
FROM [dbo].[SINGLE_CERT_ACC] SCA
INNER JOIN #TMP_DELTA_READONLY TDR
ON SCA.id = TDR.id
SET @RecsUpdated = @RecsUpdated + @@ROWCOUNT
  • 1
    Are you executing the same SP twice (or more) in the same server? – McNets Feb 14 at 17:23
  • They have the same name but different instances in each database. – SQLMIKE Feb 14 at 17:29
  • 1
    I mean, could #TMP_DELTA_READONLY or [SINGLE_CERT_ACC] be the cause of the block? – McNets Feb 14 at 17:30
  • yes I think you are correct – SQLMIKE Feb 14 at 17:34
  • A lot of times that could be index related, or lack of an index really, along with parallelism. What's the query plan look like? – Antoine Hernandez Feb 15 at 0:07
0

On further investigation I believe the update itself might be causing the locking. It is on a large table 500+ million rows. As I understand it the table will become locked as soon as SQL starts to find the join. Perhaps the way activity monitor displays the locking is a little misleading though

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.