Database Administration
sql-server t-sql stored-procedures
Updated Thu, 21 Jul 2022 22:15:33 GMT

Temporarily disable SELECT for one table (during an update)


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 SELECT queries 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.

Solution

Sorry for the late entry, but I was testing out the direction that David Browne pointed out and it works just as needed!

A more intrusive method is to put the database in SINGLE USER mode killing all the other connections.

Turns out that a database Restrict Access option has not only MULTI_USER and SINGLE_USER modes (accessible to everbody/nobody respecitvely) but also a RESTRICTED_USER:

Only members of the db_owner, dbcreator, or sysadmin roles can use the database.

Since our service accounts have those roles they still can perform their ETL duties while clients receive a native MS SQL message that the database is currently in restricted access state. Our managers notified all the clients that this message means the DB is under necessary monthly maintenance and everything will be available as soon as clients receive Data is updated email (which is sent automatically as the ETL's last step).

The downside is that changing DB state will force drop all its active connections no matter what role the user has, so make sure no important queries are going on or wait for them to finish. The upside is that all the other databases are not affected and are available as usual.

GUI path is: Database Properties Options State (the last dropdown) Restrict Access.

You can also do it programmatically:

ALTER DATABASE [Name] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
-- WITH ROLLBACK IMMEDIATE doesn't wait for active queries completion
...
ALTER DATABASE [Name] SET MULTI_USER;

Though while this method works for a monthly updated DB, I'm still working out a way to lock specific tables because the other big-tabled DB is updated constantly and RESTRICTED_USER will essentially make it never available for the clients. So I will try to update the answer later if I'll come up with something on that matter.


External Links

External links referenced by this document:

References