question

shamim007 avatar image
shamim007 asked

How to freeing unused table space from SQl server 2008R2

Hi Experts , I have a table which has 56GB unused space .how do i reclaim the unused space from table . My table looks like SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MyTable]( [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] GO SET ANSI_PADDING OFF GO as far as i know normal index defrag will not fix this in MSSQL 2008.do i have to change the table structure?what is the best way to get unused space from table ?
sql-server-2008-r2table-usage
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
From the table definition this appears to be a heap table. Even if it does have some non-clustered indexes, any attempt to defrag them is pretty much meaningless. It will not reclaim any space either. It is very common for heap tables to have a bit of unused space. The tables with clustered index can have this problem too, but usually as a result of dropping variable length column(s). There are few questions to consider here: - Why is this a heap table? - Why PdfData column is image? It should probably be varbinary(max) - Were there any variable length columns dropped from it recently (this may cause excessive unused space in any tables where they are heap or not) Please read the post by @Paul Randal about the [heap fragmentation][1]. Yes, there is a way to "defrag the heap" by running **alter table dbo.MyTable rebuild** but please read the post to understand all implications. To reclaim space you might want to consider running [dbcc cleantable][2] of course, but it would be much better to create the proper (narrow, ever-increasing, never-changing) clustered index on this table. Here is the sample script (please do not run it before considering all options): dbcc cleantable (YourDatabaseName, 'dbo.MyTable', 0); go Oleg [1]: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/ [2]: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.