6

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)

sys.dm_db_index_physical_stats

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.

Improve this question
4
  • 1
    Can we get a create table statement, including keys and indexes? Based on your description, it sounds like this is a heap.
    – mathewb
    Commented Nov 2, 2017 at 16:14
  • 2
    And how are you measuring the size. With sys.dm_db_partition_stats?
    – David Browne - Microsoft
    Commented Nov 2, 2017 at 16:15
  • Can you also include the results for sys.dm_db_index_physical_stats?
    – mathewb
    Commented Nov 2, 2017 at 18:53
  • Isn't VARCHAR(MAX) stored in heap?
    – edze
    Commented Nov 2, 2017 at 21:13

3 Answers 3

Reset to default
2

Your table has a lot of LOB data, and the only thing that can free up LOB pages is index reorg (with lob_compaction): 

ALTER INDEX PK_LARGE_LOG_TABLE REORGANIZE WITH (LOB_COMPACTION = ON)

Index rebuild will not even touch LOB data, and backup/restore will bring your database to exact same state as it was at the backup time.

Improve this answer
2
  • As noted in the first edit, we've already run the index reorganize with (LOB_COMPACTION = ON) with out any change in reported table size. As you noted below, the backup / restore wouldn't seem to provide any value. Does this mean that a database shrink is required prior to the reorganize with LOB_COMPACTION = ON?
    – P Hinman
    Commented Nov 3, 2017 at 15:00
  • >>> Does this mean that a database shrink is required prior to the reorganize with LOB_COMPACTION = ON?<<< No, database shrink will change nothing respect to table size. It does not touch pages content, it only moves pages from the end of the file to file's beginning, so if now you have 626878 + 22401374 pages in your table, after shrink you'll have the same number of pages. Are you sure that your 600000 rows had LOB data? Maybe it was always in-row data? Did you measure how many in-row and LOB pages was in your table before delete (what we see in your picture is the AFTER delete result)
    – sepupic
    Commented Nov 6, 2017 at 10:41
1

@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:

  1. Rebuild the clustered indexes of the table.
  2. 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.

Improve this answer
2
  • >>>Then backup and restore the database<<< And what do you think to achieve by doing this??? After restoring you'll get the exact copy of database as it was at the backup time.
    – sepupic
    Commented Nov 3, 2017 at 8:06
  • Can you take the backup and restore it elsewhere to check your hypothesis? Or if not, consider renaming the log table, creating a new copy of it with the same structure, and then performing the rebuild on the renamed table. Depending on how the log table is used, this might be possible.
    – mathewb
    Commented Nov 3, 2017 at 16:13
1

Had same issue and found following info related to applied SP/CU:

FIX: Cannot reclaim unused space by using shrink operation in the table that contains a LOB column in SQL Server

I leave it here in case it helps someone!

Best regards,
Marios

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.