How to reclaim unused space from clustered index table

Question

I have a clustered index table with more than 11,000,000 records running on SQL Server 2008 R2 Standard Edition. I have noticed the table has about 90 GB of free space.

This is the table definition:

CREATE TABLE [dbo].[nsPDFDocument](
    [DocumentID] [varchar](100) NOT NULL,
    [DocumentDate] [char](10) NOT NULL,
    [DocumentTime] [char](10) NOT NULL,
    [DocumentSize] [int] NOT NULL,
    [PDFData] [image] NULL,
    [FileName] [varchar](255) NULL,
    [OwnerID] [varchar](50) NULL,
    [Title] [varchar](150) NULL,
    [CreationDate] [char](10) NULL,
    [CreationTime] [char](10) NULL,
    [Provisional] [char](1) NULL,
    [PreviousVersionID] [varchar](100) NULL,
    [isLockedBy] [varchar](50) NULL,
    [SecondaryStorageURI] [varchar](150) NULL,
    [PreviousExportUri] [varchar](150) NULL,
    [PurgedDocumentHash] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have tried:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET NOCOUNT ON;

CREATE TABLE #PDFsToFix(
       ID    INT IDENTITY NOT NULL PRIMARY KEY,
       DocID VARCHAR(100) NOT NULL
);

CREATE TABLE #OneBatch(
       ID    INT          NOT NULL PRIMARY KEY,
       DocID VARCHAR(100) NOT NULL

);

INSERT INTO #PDFsToFix( DocID )
       SELECT DocumentID
       FROM dbo.nsPDFDocument
       WHERE CAST(PDFData AS VARBINARY(MAX)) = 0x00;

DECLARE @processedID INT = 0;
DECLARE @batchSize   INT;
DECLARE @changed     INT

WHILE EXISTS( SELECT * FROM #PDFsToFix WHERE ID > @processedID )
BEGIN
       TRUNCATE TABLE #OneBatch;
       INSERT INTO #OneBatch( ID, DocID )
              SELECT TOP 50 ID, DocID FROM #PDFsToFix WHERE ID > @processedID ORDER BY ID;
       SELECT @batchSize = COUNT(ID), @processedID = MAX(ID) FROM #OneBatch;

       BEGIN TRANSACTION
       UPDATE dbo.nsPDFDocument SET PDFData = NULL
       WHERE DocumentID IN( SELECT DocID FROM #OneBatch ) 
          AND CAST(PDFData AS VARBINARY(MAX)) = 0x00;
       SET @changed = @@ROWCOUNT;
       IF @changed <> @batchSize
       BEGIN
          ROLLBACK;
          RAISERROR( N'Updated %d rows, expected %d', 10, 1, @changed, @batchSize );
          BREAK;
       END
       ELSE
       BEGIN
          PRINT 'Updated 50 rows';
          COMMIT;
       END
END

After running that query I did not get good result.

What steps should I follow to regain space from the table?

Note: I run a "Reorganize" index maintenance job every weekend; that does not help at all.

Answer 1

You may need to run an index Rebuild (not reorganise) on the clustered index - note in standard edition this will lock the table for writes for the duration of the rebuild (and reads may be affected), which could be some hours.

Otherwise a simple DBCC SHRINKFILE might work, but be aware of this: https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/