Sql-server – dropping SYNONYM is blocking in sql server

blockinglockingsql serversql-server-2012t-sql

I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2.

Now my problems is: while users querying from synonyms which points to TestDB1 is still running it won't let me drop synonyms. It is blocking the process synonyms re-point to TestDB2.

It only let me re-point after queries complete. Is there any other process to overcome this problem. I want to re-point synonyms while users querying?

Note: Both databases contains same tables but data will be change

Best Answer

No, dropping a synonym requires a Sch-M lock, and also IX locks on sysschobjs/sysobjvalues, at least if it is on the same server (I haven't tested what happens when the synonym uses a 4-part name).

What exactly would it mean if you could change a synonym in the middle of a query or transaction? If you have a query plan operator or a cursor which needs to go back to the table, or you have multiple steps in a transaction that reference the table, it would violate all kinds of principles if the referenced table were different. I think it is much better that you can't change it until a point where nobody is referencing it.

EDIT :

If you implement AlwaysON (which fits in your situation), below is my recommendation:

Assuming (from your post) DB1 is the main database and DB2 is for reporting.

Configure DB1 as primary in AlwaysON. You get a warm standby database on the secondary server (say DB1_Replica).

Configure DB1_Replica as readable and have asynchronous-Commit mode configured.

Under asynchronous-commit mode, the secondary replica never becomes synchronized with the primary replica. Though a given secondary database might catch up to the corresponding primary database, any secondary database could lag behind at any point. Asynchronous-commit mode can be useful in a disaster-recovery scenario in which the primary replica and the secondary replica are separated by a significant distance and where you do not want small errors to impact the primary replica or in or situations where performance is more important than synchronized data protection. Furthermore, since the primary replica does not wait for acknowledgements from the secondary replica, problems on the secondary replica never impact the primary replica.

The listener is going to help you with seamless failover.

Refer to below links to get you started :

SQL Server – Resource Cost of a Blocked Query

A request that is being blocked does no work. Here's an example:

Create a blocking query

use AdventureWorks2012;
go

begin tran;
    update HumanResources.Department
    set Name = 'testing 1 2 3'
    where DepartmentID = 7;
--rollback tran;

Run a query that will be blocked by the above session

use AdventureWorks2012;
go

select *
from HumanResources.Department;

Now look at any "work" this blocked request could be doing (query sys.dm_exec_requests for this blocked session, and then wait some time and query again to compare/contrast):

select
    start_time,
    status,
    total_elapsed_time_sec = 
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

waitfor delay '00:00:10';

select
    start_time,
    status,
    total_elapsed_time_sec = 
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

enter image description here

What you see here is a request that is doing no work, as it is not in the running state. When it is suspended then it is not on a processor doing any work.

Best Answer

Related Solutions

Sql-server – SQL Server Replication questions

EDIT :

SQL Server – Resource Cost of a Blocked Query

Related Question