Deleting LOB Data and Shrinking the Database

While attending SQL Saturday 194 in Exeter over in England one of the attendees came to Mladen Prajdić, Andre Kamman and myself with an interesting problem.  She had a database table which was about 200 Gigs in size which she wanted to delete about half of the data from the table.  The catch was that the database table was full of LOB data where the rows were very large, with an average LOB data size of over a meg.  She also needed to shrink the database after the database was deleted so that she could reclaim the space from the database.  Oh and all this had to be done on SQL Server 2005 Standard Edition. (Everything here applies to SQL Server up through SQL Server 2012 as well.)

Deleting the data from the database is the easy part, a simple delete loop will handle that nicely.  The problem is when you delete rows from a table which contains LOB data the LOB pages aren’t cleared when they are deallocated.  We can see this by running the following code.

use Lobtest
CREATE TABLE t1 (c1 int IDENTITY(1,1) PRIMARY KEY, c2 ntext)
INSERT INTO T1 (c2) VALUES (replicate('a', 20000))
DBCC IND ('LobTest', 't1', 1)
DBCC TRACEON(5201, -1)
DBCC IND ('LobTest', 't1', 1)
DECLARE @dbid as int = db_id('Lobtest')
DBCC PAGE (@dbid, 1, 231, 3)

You can see that page 231 is a LOB page which is allocated to the table t1. When you look at the actual page using DBCC PAGE after the row has been deleted we can see that there is data in the page, and that the page header shows that the page is still allocated to the table t1. This can be seen by looking in the header of the page for the header value labeled “Metadata: ObjectId = 245575913”.

When you go to shrink the database the SQL Server engine will get to the LOB pages and it will need to figure out if the LOB row is a part of a row which still exists or not. In order to do this SQL Server will need to scan through the pages which make up the table looking for any rows which reference the page it is trying to delete.

When doing shrinks after deleing large amounts of LOB data SQL Server will generate large amounts of IO while figuring this out and the shrink operation will take an extremely long time. (Paul Randle talks more about it here.)

So the question that this person at SQL Saturday had was, how can I reclaim the space from my database within a reasonable time.

The solution that we came up with was actually pretty simple. Do the database deletion as normal. Then backup and restore the database. Then do the shrink, followed by rebuilding the clustered indexes in order to fix the fragmentation issue which the shrink will introduce.

This works for a pretty simple reason, because the PFS page shows that the LOB page isn’t allocated even though the page is full of data (you can verify this by looking at page 1 in file 1 in the sample database created by the script above). When the database engine backups up the database the database engine looks at the PFS pages to figure out which pages to back up. Because the PFS pages show that the pages are empty the database engine doesn’t bother to backup the pages, so when the pages are restored they are restored as blank pages. This means that after the restore the shrink operation can run without an issue.

In the case of this application there was a maintenance window which could be taken advantage of which would allow the backup and the restore to happen.

Another option which we came up with which would require less downtime involved using database mirroring. By configuring database mirroring (which is initialized via a backup and restore process giving us the same basic approach) and then failing over to the mirror we would end up in the same position. We could then shrink the database without issue (probably pausing database mirroring so that we didn’t have to wait for the second server to process the shrink in real time) and then fail back the database to the original server.

As geeky as it was, Mladen, Andre and I had a great time figuring this out, and the attendee had a great time watching us go through all the possible options as we excluded them one by one. And most importantly she got her problem solved.

So if you end up in this situation here’s a solution that will help you shrink the database so that you can reclaim the space that the LOB data pages are taking up without having to wait forever.



5 Responses

  1. You can reclaim space by just rebuilding the clustered index. I did in our production environment and it helped to shrink table size from 80GB to 8 GB.

  2. I just tested this by doing a rebuild after deleting data from a table and the LOB pages were still populated and allocated to the table.

  3. Denny,

     I am not sure if I understand the method of shrinking properly. Instead of deletion, I have set the lob values to 0x00001 due to foreign key constraints. I have disabled the mirroring. So the space of DB is 500 gb, but the min required (as per shrink DB pop up) is 400 gb. Looks like reducing the LOB values to 0x00001 saved me from 100gb. No I want to reclaim that space. I dont get that with index rebuild and the shrink DB is taking way way way too long for the DB to be down and this is the production. Any advice ?
  4. f22raptor,

    The pages are deallocated so SQL thinks that it can shrink them. But I bet if you go look at the actual pages that they are still full of data which means that SQL still has to check the values to see if they are actually still relevant or not. There’s not a lot of really good options at this point.

    Unless you really need to reclaim the drive space I would just leave the database alone. SQL will reuse the deallocated pages as it needs to allocate more space. So eventually those pages will get used again.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?