Pages

Friday, July 8, 2011

Deleting data from heap table

We started running out of space on a few of our servers. Adding more space is an easy getaway, but everyone knows the perils of throwing more and more hardware to solve space issues. So determined to use the available space, we decided to look at any particular tables which had a lot of data and could be deleted or archived. We figured that there was a heap table used in every databases, in some as big as 200GB, which had data from when the system had started. Also new data was being added to that table everyday. After careful consideration of keeping back dated data upto one year and deleting the rest since we had flat files of data over many years which could be loaded if required, we decided to delete the data from heap.
The data was deleted in batches since the table was continuously used and we din't want to lock it down for long time. But as everyone knows the major issue with deleting data from a heap is the data is deleted but pages are not deallocated, unless you do a rebuild or have an index over it or delete with a TABLOCK.
Creating an index though an option was not going to work for us, since it would take nearly as much space as heap. We did not have that much space on server. Rebuilding also took a lot of space and hence with only a few GB remaining even this was not an option.

So for the time being we decided to delete 500 rows at a time with TABLOCK option. With TABLOCK, the delete acquires a lock on the table and deleted data as well as deallocates the pages. So we planned to run and monitor this so as to not have other queries wait on it and detect blocking early on.

Once we got a lot of space back and the heap had only one year of data in all databases, we decided to create a new table with index and have the queries use this and delete the heap.
The other option was to use a compressed table while doing so, so that the space required for a years data would be pretty less.
We decided to drop the heap once the new table was in place and data from heap was transferred to it.
Next, we put a job in place which would run weekly or monthly and delete data from the table, so that the table remained manageable with only one years data preserved.

These were a few options we used to sort our space issues, other than deleting unused indexes. Creating required indexes etc. If you know of any other options please do share your thoughts.

No comments:

Post a Comment