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) FROM syslogins WHERE name like '%client%' PRINT @queryDisable; 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.