Sql-server – Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database

sql serversql-server-2012standard-edition

Running SQL Server 2012 standard, using both replication data and log shipping. Transaction log backups are created every few minutes.

This question is NOT about the amount of space reported by the file system.

We have a log table with four VARCHAR(max) columns. The table currently holds roughly 180 GB of data. The table grows at a rate of roughly 10 GB per month.

In an attempt to free up space for future growth, I recently deleted (in chunks over a 36 hour period) close to 30% of the data.

Based on the size of the table and the number of rows being removed, I expected the size of the table to be reduced by roughly 55-60 GB. I verified this by taking a sample and calculating the average row size, and then multiplying that by the number of rows to be removed. However, after finishing the delete, the table size has not changed.

While I am not looking to shrink the size of the .mdf file, I did check to see if the database had any free space that could be shrunk, but there is 0% free space.

So – I am confused. I've verified the data that I deleted from the table is no longer there. I even compared it against a backup to get a before & after. The number of rows in the database has changed, but the data space for the table has not decreased.

My questions then are:

1) Why did deleting data from the table not reduce the size of the table?
2) What can I do to actually reduce the size of the table?

EDIT:

I am determining the space used by the table by calling sp_spaceused 'tablename'.
I have used dbcc updateusage (0, tablename) prior to calling sp_spaceused.
I am NOT trying to free up disk space – although an analysis indicates that there is 0% space available to be reclaimed.
I have run an index reorganize with ( LOB_COMPACTION = ON ). Fragmentation is below 1%.
The table is not a heap. Table definition below.

 CREATE TABLE [dbo].[LARGE_LOG_TABLE](
  [ID] [varchar](36) NOT NULL,
  [DOC_ID] [varchar](36) NOT NULL,
  [REQUEST_DATA] [varchar](max) NULL,
  [REQUEST_DATA_XSLT] [varchar](max) NULL,
  [RESPONSE_DATA] [varchar](max) NULL,
  [RESPONSE_DATA_XSLT] [varchar](max) NULL,
  [ACTIVE_FLAG] [varchar](1) NULL,
  [CREATED_BY] [varchar](100) NULL,
  [DATE_CREATED] [datetime] NULL CONSTRAINT [DF_LARGE_LOG_TABLE_CREATE_DT]  DEFAULT (getdate()),
  [USER_LAST_UPDATED] [varchar](100) NULL,
  [DATE_LAST_UPDATED] [datetime] NULL CONSTRAINT [DF_LARGE_LOG_TABLE_LAST_DT]  DEFAULT (getdate()),
 CONSTRAINT [PK_LARGE_LOG_TABLE] PRIMARY KEY CLUSTERED 
 ( [ID] ASC ) ON [PRIMARY] ) 
 ON [PRIMARY] 
 TEXTIMAGE_ON [PRIMARY]

EDIT 2:

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), OBJECT_ID('database..LARGE_LOG_TABLE'), default, default, default)

EDIT 3:

The answers provided to the question at Delete query does not appear to open space on the SQL Server refer to freeing up space on disk / shrinking the physical size of the database. This question refers to removing rows containing LOB data and yet the database (internally) shows the LOB data has not been removed. We removed 600000 rows of data, but the space used on the table level (not the file system level) was only reduced by a few bytes – not the expected 60 GB. This question is about removing the LOB data.

Best Answer

@edze asked a great question. I've known that LOBs are stored separately from the table data (with a pointer referencing the storage location), but I didn't know specifically where they were stored.

While searching for more information on where LOB data is stored, I found the following links:

http://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/

The key take away here (focused on a database shrink) is the following:

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.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f80ad8c7-e6c9-4538-a89b-8d6727050b9c/release-space-used-after-sql-server-delete-in-rows-with-blob-varbinarymax-using-textimage-on?forum=sqldatabaseengine

Here the key is:

Rebuild the clustered indexes of the table.
Execute DBCC UPDATEUSAGE (0)

With this being Standard edition, a rebuild will require a LONG lock - but it looks like the only way to recover the space.

Related Solutions

Sql-server – Deleting rows from a table with a very small amount of expansion space

As things stand, the query:

DELETE TOP (4000)
FROM   [OLD_TABLE] WITH (TABLOCKX)
WHERE  [Date] < '2014-01-01 00:00:00'

...has to scan the whole heap table, testing each row it finds, until it eventually finds 4000 to delete. On the next iteration, the whole business starts again from square one. Assuming the scanning process is performed in the same (allocation unit) order each time, these scans will take longer and longer to find 4000 rows as time goes by.

Creating an index on the [Date] column will allow SQL Server to find the 4000 rows much more efficiently. It does add a small overhead to each delete (as the new nonclustered index needs to be maintained as well) but this is nothing compared to the effort that will be saved by not performing a scan each time. The index will also require a certain amount of space, but it should not be too large. You should create the following index before resuming your data removal process:

CREATE NONCLUSTERED INDEX <index_name>
ON dbo.OLD_TABLE ([Date]);

By the way, if the database has snapshot isolation or read committed snapshot isolation enabled (even if not actively used!), the TABLOCKX hint will not be enough to ensure that empty heap pages are deallocated. Your heap table may therefore contain many empty pages - a concern since you are so low on space.

The standard way to address this space management issue is to create a clustered index (or issue an ALTER TABLE REBUILD statement, but that requires SQL Server 2008). It seems that creating a clustered index might also not be an option for you, due to space constraints. There isn't an obvious way to resolve this right now, given the space issue.

One thing to keep an eye on while the delete is progressing is the space used by the transaction log. If the database is in FULL or BULK_LOGGED recovery, you will need to keep on top of backing up the log. If the database is using SIMPLE recovery, you may need to issue a manual CHECKPOINT from time to time to release transaction log space for reuse (otherwise the physical file might grow).

If you can get hold of some additional temporary storage, a better way might be to bulk export the data you want to keep to a safe location, drop the table, recreate it (preferably with a clustered index!) and reload the saved data. This is generally faster than the incremental-delete process, but it depends on your objectives and priorities.

Sql-server – Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

Interesting question, I created both table1 and table2 in SQL Server 2014 and populated 2000 rows of the same data you have provided. When checking the amount of space consumed by each table, here is what I got:

table1 (varchar) - 392K
table2 (bit) - 168K

However when I alter all the column types in table1 to match table2, the space used grew to 540K. After rebuilding the table, the space drops down to 160K:

alter table table1 rebuild;

So you should see the disk space savings using the leaner data types after reclaiming the used space. Best of luck.

Best Answer

Related Solutions

Sql-server – Deleting rows from a table with a very small amount of expansion space

Sql-server – Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

Related Question