Temporarily disable SELECT for one table (during an update)
Marcee last edited by
We have a dedicated data mart SQL Server with several really big tables that are updated one partition at a time via parametrized SSIS loop. Quite often when clients try to read such a table during an update, they get in between ETL loop steps, so the whole table is locked until all the reads are finished, and that goes on and on for hours.
So how do I:
- drop all active
SELECTqueries for a specific table,
- disable all operations on it for everyone except an ETL account,
- and make it readable again afterwards?
A very crude temporary solution that I came up with is just disabling all the client accounts during the big update time:
DECLARE @queryDisable NVARCHAR(MAX) = N'';
SELECT @queryDisable += 'ALTER LOGIN ' + QUOTENAME(name) + ' DISABLE;' + CHAR(13) + CHAR(10)
WHERE name like '%client%'
EXEC sp_executesql @queryDisable;
But that locks them out of the whole server while they may need to access a different database which is not being updated at the time.
I hope there is more elegant and civilized way than bruteforce DENY/GRANT cycling of SELECT permissions on every account there is.
Edit 2022-03-04: Thank you for your answers, they are duly noted and I will report back on progress, but we're making big releases every 1-2 months so it may take some time.
- drop all active
briley last edited by
In a transaction ALTER the table. You will get an exclusive schema lock for the duration of the transaction. Requiring a transaction in SSIS may be enough, alternatively set RetainSameConnection on the connection manager and handle the transaction and schema lock in a TSQL step.
You can get the schema lock with any alter table, like ALTER TABLE . . . SWITCH, or with SP_RENAME. A cheap and harmless ALTER TABLE for any table that doesn't have any disabled constraints is something like
alter table SomeTable with nocheck check constraint all
Which is a noop, except that it has the side effect of requiring your transaction to hold an SCH-M lock.
A more intrusive method is to put the database in SINGLE USER mode killing all the other connections.