1

I had a user doing a massive SQL data compare from server A to server B involving database X.

On Server B, I was trying to run a CREATE SYNONYM statement in database Y, which was being entirely blocked -- as in the statement was running for approximately 20+ mins. There were no open transactions in database Y on server B.

I was wondering what could be causing this, and if it is preventable via server configuration, so I can pass any information along to my sys admin and DBA. Thanks in advance for any insight that might help performance.

According to sp_who2:

Create Synonym blocked by INSERT into master by me

INSERT into master by me blocked by INSERT into master by sa

INSERT into master by sa blocked by massive EXECUTE from other user (for SQL compare / sync)

Improve this question
2
  • If you can recreate this situation, install and run sp_whoisactive to determine why your process is waiting
    – Scott Hodgin - Retired
    Commented Sep 8, 2016 at 9:18
  • @ScottHodgin Thanks for your reply -- let me know if the additional info I provided helps get to the bottom of this at all. Luckily, I saved the dump from sp_who2
    – ccrook
    Commented Sep 9, 2016 at 12:49

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.