Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my production environment.
Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space.
Otherwise, you're looking at:
- ALTER TABLE (mytablename) REBUILD - which takes it offline
- Doing deletes with TABLOCK hints
- TRUNCATE TABLE (mytablename)
I know some folks think it's trendy, but heaps just aren't a good fit for active OLTP systems that have to deal with deletes (which cause the empty space problem) and updates (which cause the forwarded fetches problem.)
You can reclaim the space by shrinking.
From SQLskills SQL101: Why does my heap have a bunch of empty pages? by Paul Randal (emphasis added):
On the extreme end (in my opinion), you could reclaim the empty heap space using a shrink operation. Shrink won’t free up space inside pages as it moves them (with the exception of compacting LOB pages as it goes – somewhat unsuccessfully depending on which version and build you’re on – see KB 2967240), but it will remove empty pages rather than moving them. This will effectively shrink the heap after a large delete, but with the usual caveats about shrink causing index fragmentation and generally being an expensive, slow operation to perform.