1

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]

enter image description here

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.

  • How do you know the table has 90GB free space? – Gareth Lyons Nov 2 '17 at 15:04
  • i checked by :SSMS-Database-reports-standard reports-disk usage by top tables @GarethLyons – shamim Nov 2 '17 at 15:14
  • Does the database itself show the same amount (or more) of free space? (Right click on db -> Properties -> Space Available value, or in Standard Reports, Disk Usage). Ignore the Transaction Log Space values. – Gareth Lyons Nov 2 '17 at 15:44
  • from standard reports ,disk Usages ,unused about 90 GB @GarethLyons – shamim Nov 2 '17 at 16:17
  • 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: littlekendra.com/2016/11/08/… – Gareth Lyons Nov 2 '17 at 16:33
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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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