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.
Best Answer
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/