2

A read-only database (database1) is updated via log shipping every minute, then a procedure queries multiple tables in it and refreshes a large table on database2.

For this process I

  • run an "expensive" query against database1 and generate a new Table in database2 (Table_A_TEMP)

    BEGIN TRAN
    DROP TABLE Table_A
    RENAME Table_A_TEMP to Table_A
    COMMIT
    

Does it look too ugly? Otherwise I would have to create a very complex verifications and updates.

Suggestions to minimize locks?

3
  • SQL Server is going to have to take out exclusive locks to drop and rename objects. If something is trying to access the table while you are doing it I don't thing there is much you can do to limit the blocking. What locks are you trying to minimize? Are you refreshing Table_A with every log ship or on set intervals?
    – Aaron
    Oct 28 '15 at 21:42
  • How is a read only database able to be updated? Oct 29 '15 at 7:33
  • Database1 is read-only, not database2.
    – Ricardo
    Oct 30 '15 at 16:27
0

I have to update every minute. One person suggested me to rename the original table as well, I will try, it will probably be quicker, then I drop it after the commit.

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.