After working with Microsoft the workaround was found:
There is a known issue where on a database having a LOB data in it, the SHRINK operation will only shrinks one empty LOB extent at a time. This is by design. And hence we may have to shrink multiple times to release the space to operating system. This behavior is because we do not deallocate the LOB pages; we save them for the next time we may need to insert LOB data. In certain cases, these allocated but empty LOB pages may accumulate. Again, this is by design.
Also When the LOB value to be inserted is larger than one page, we break it up into page-size fragments. And when we insert a page-size fragment, we don't search the existing LOB pages for free space; we instead just allocate a new page. This is an optimization: in the typical case, the free space fragments on the existing pages are all smaller than a page. Since we need a whole page of free space, we might as well skip the search and just allocate a new page.
NOTE: This behavior is by design and have been reported as a known issue.
Considering the above know behavior of SQL , which is by design the work around to release the empty LOB pages is as follows:
Use DBCC CLEANTABLE('databasename', 'tablename') to deallocate all the empty extents.
NOTE: The recommended option to delete all rows of a table is to use TRUNCATE option. Truncate has advantage over the Delete operation as I had mentioned in the previous email.
On your production database backup which I have restored at my end, when I ran the DBCC CLEANTABLE command. The sp_spaceused output after following the workaround was as follows:
Please run DBCC UPDATEUSAGE query on the table before you run SP_SPACEUSED query. Please refer link for more details: http://msdn.microsoft.com/en-us/library/ms188414(v=SQL.90).aspx
• Pages and Extents
• Managing Extent Allocations and Free Space
• Managing Space Used by Objects