DELETE on a heap table 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.)