Is it possible for a table to increase size when deleting records?

No transaction log involved, only the table itself.

Use sp_spaceused "table_name" to check the space used.

It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.

Recovery mode set to be simple, autoshrink turned on.

The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.

Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue

Thanks!

First of all, you really should turn auto-shrink off. It causes lots of problems with performance and fragmentation.

You probably are seeing index and table fragmentention during the delete. What I would do would be to turn autoshrink off, then if I was very concerned about table size, I would either ALTER INDEX REBUILD or ALTER INDEX REORGANIZE after a large delete to cleanup and shrink the indexes.

|||When you delete a record in SQL Server, the record normally does not get deleted immediately. What happens is that we set a bit in the record to indicate that the record is deleted (this is called the ghost bit).

There is a background process, ghost cleanup task, that runs every 15 seconds and tries to remove records from pages. For indexes, the ghost cleanup task will also remove pages that get empty. For heaps, the ghost cleanup task will not remove pages that get empty

So if your table is a heap, and you are doing a lot of deletes, it is possible that the size of your table does not get smaller. In case of heaps, we will only deallocate pages when we have a table lock on the heap.

There is one other corner scenario where during deletes a record can actually grow in size. For this scenario, you need to have snapshot isolation enabled on the database. When you delete a record during snapshot isolation, we will ghost the record, but we will add version related information (14 bytes) to the record so that snapshot isolation works correctly. Any ghosted records that are part of active transactions cannot be removed by the ghost cleanup task. So in this case, during a delete, you will actually need more space than before, as we need to store the version information for each record as well, potentially adding 14 bytes per record.

For heaps, this might even get worse. If a record gets ghosted and grows with 14 bytes, we might actually have to forward the record to another page if it does not fit on the current page anymore, meaning that you might see a lot of extra space being used. Again, this only happens when snapshot isolation is turned on and you have a lot of pages in the heap that are almost full. Also, the average record size needs to be less than 32 bytes for this to happen, otherwise we do some things in the engine to avoid the forwarding.

Hope this helps,