May 5, 2011 at 7:11 am
I currently look after a VLDB which is 2TB+ in size, the database started its life in SQL 2000 and was ported to 2005 a few years ago, planned upgrade to 2008 is on the cards. The DB acts as a storage container for XML messages in ‘nText’ fields, moving to varchar(max) soon, each table has a clustered index which are stored in its own file group while the TEXTIMAGE (blob data) are stored within a separate filegroup.
The main table which has the bulk of the messages is just under 1.5 TB in size with 140 million rows. We have recently introduced an archive process which marks the rows that are available for archiving, another process follows up which copies the records to the archive DB then nulls the blob field, a third process which runs overnight then deletes the row which were previously nulled.
What we find is that the space returned to ‘free space’ within the table is not as expected because when we run ‘sp_spaceused’ on each of the tables each morning and last thing at night is that the unused space increasing dramatically, we now have at least 100+ GB of unused space in the main table.
The archive process was put in place to reduce the size of the database in order to pass on cost savings to the customer, we therefore ran the DBCC command SHRINKFILE with the EMPTYFILE option, this has worked successfully in the past but took several weeks to complete.
The problem that I now have is that the SHRINKFILE does not seem to be doing any positive activity as the unused space amount is not going down as expected, which did happen in the previous successful attempt of SHRINKFILE.
How can I determine if the SHRINKFILE command is doing anything positive as I suspect that the archive process which is a new faster process is now outperforming the SHRINKFILE operation and adding more unused space than the SHRINKFILE operation can remove?
Any advice on how to get rid of unused space on VLDB’s where blob data has been deleted would be greatly appreciated, all I have ever found is to export the table data then import to a new table but on a table which has 1.5 TB of data and 140 million rows would take more than a few maintenance windows!!
May 5, 2011 at 7:18 am
Try Alter Index ... Reorganise with the lob compaction option.
You need to either rebuild or reorganise after that shrink anyway.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2011 at 8:51 am
The shrinkfile operation runs continuous all week, it stopped on a Monday evening where the indexes are rebuilt but not with LOB compaction, we tried LOB compaction on the DR system made no difference.
May 5, 2011 at 9:46 am
Shrink is never going to remove space that's unused in a table. It only works on unallocated data pages. It will be slow as hell if you have LOB data, because of the way LOB data is stored, and it won't help you much.
Stop shrinking (if you're shrinking then the rebuild index job was a waste of time as the shrink will have re-fragmented all of those indexes)
Rebuild or reorganise of the clustered index should reduce the amount of unused space within a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2011 at 1:23 pm
Rebuild or reorganise of the clustered index should reduce the amount of unused space within a table.
We do a reindex of the database each week regardless if it requires it or not, we regain on average between 12 and 18 GB of free space back BUT the unused space is always still there, The index is within its own file group while TEXTIMAGE (nText data) is in another file group. The rebuild of the index is an ALTER index and not REORGANISE, we have tried REORGANISE but get the same results, we attempted this on the DR DB.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply