CODEWITHSUNDEEP
sql-serversql-server-2008-r2truncate
DJBradley76
DJBradley76

Reputation: 21

How can I block users while I truncate a SQL Table

We have a SQL Server 2008R2 Table that tracks incremented unique key values, like transaction numbers, etc. It's like a bad version of sequence objects. There are about 300 of these unique keys in the table. My problem is that the table grows several 100,000 rows every day because it keeps the previously used numbers. My issue is that we have to clean out the table once a week or performance suffers. I want to use a truncate and then kick off the SP to generate the next incremented value for each of the 300 keys. This works with a run time of about 5 minutes, but during this time the system is trying to use the table and throwing errors because there is no data. Is there any way to lock the table, preventing user access, truncate and then lift the lock?

Upvotes: 2

Views: 2314

Answers (2)

Phil Pledger
Phil Pledger

Reputation: 476

This will require cooperation by the readers. If you want to avoid using a highly blocking isolation level like serializable, you can use sp_getapplock and sp_releaseapplock to protect the table during the regeneration process. https://msdn.microsoft.com/en-us/library/ms189823.aspx

An alternative might be to build your new set in another table and then use sp_rename to swap them out.

Upvotes: 1

Dave Clough
Dave Clough

Reputation: 100

TRUNCATE automatically will lock the whole table. A delete statement will implement row locking, which will not interfere with your user queries. You might want to think about a purge of old records during off hours.

Upvotes: 3

Related Questions