How to free table space

Question

Post reply

How to free table space

Viewing 15 posts - 46 through 60 (of 65 total)

You must be logged in to reply to this topic. Login to reply

miksh Ten Centuries Points: 1344 More actions · Answer 1

CirquedeSQLeil (9/28/2010)
I find that extremely odd. Can we get the DDL for this table? Including the indexes?

CREATE TABLE [MySchema].[MyTable](

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[RcID] [int] NOT NULL,

[WgID] [int] NOT NULL,

[PCode] [char](3) NOT NULL,

[MTP] [smallint] NULL,

[IsFinal] [bit] NOT NULL,

[DataXML] [xml](DOCUMENT [MySchema].[SomeValues]) NOT NULL,

[LastUpdated] [datetime] NOT NULL,

[DataSource] [char](4) NOT NULL

) ON [PRIMARY]

and currently no indexes at all.

Note that I had no issues for 4 other tables with similar structure. The only difference was that this table has over 8GB data but others less than 3GB.

And we are 100% certain that there are no forwarded records?

In this situation I'm afraid to be 100% sure ;-). How to double check it?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Quick and Dirty script

SELECT DB_NAME(database_id) as DatabaseName,

object_id,

OBJECT_NAME(OBJECT_ID,database_id) as TableName,

forwarded_record_count,

index_type_desc

FROM sys.dm_db_index_physical_stats (DB_ID(null), OBJECT_ID(null), NULL , NULL, 'DETAILED')

GO

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

miksh Ten Centuries Points: 1344 More actions · Answer 3

Yes, might be an issue - I'll investigate tomorrow but now here is results of your script

DatabaseNameobject_idTableNameforwarded_record_countindex_type_desc

Test2045250341MyTable0HEAP

Test2089058478sysdiagramsNULLCLUSTERED INDEX

Test2089058478sysdiagramsNULLNONCLUSTERED INDEX

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I presume that is after you removed the clustered index. Run that query tomorrow prior to removing any indexes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

CirquedeSQLeil (9/28/2010)
I presume that is after you removed the clustered index. Run that query tomorrow prior to removing any indexes.

The dbcc showcontig with tableresults output he posted earlier has a field for Forwarded Records..which is what I was looking for then too. Even if there were though, a truncate (or delete with tablock) on a heap would clear those out. And they could only get there to begin with if there was no clustered index when data was inserted..but since the clustered index was also the PK, I doubt that was the case.

Whisper9999 Hall of Fame Points: 3012 More actions · Answer 6

Whisper9999

Hall of Fame

Points: 3012

September 28, 2010 at 4:41 pm

#1228713

Did you try DBCC CleanTable out of curiousity?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Whisper9999 (9/28/2010)
Did you try DBCC CleanTable out of curiousity?

He said he did - early on in the thread

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Whisper9999 Hall of Fame Points: 3012 More actions · Answer 8

Whisper9999

Hall of Fame

Points: 3012

September 28, 2010 at 5:28 pm

#1228720

Oops!

miksh Ten Centuries Points: 1344 More actions · Answer 9

Yes, I did DBCC CleanTable. Also, in my earlier results for DBCC SHOWCONTIG the ForwardedRecords=0 as any other field except ScanDensity=100.

I'll try to contact MS and let you know.

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 10

If the table has < 2000 rows, then you probably wont notice much performance impact anyway while you drop/recreate indexes; that should take care of it.

miksh Ten Centuries Points: 1344 More actions · Answer 11

After working with Microsoft the workaround was found:

There is a known issue where on a database having a LOB data in it, the SHRINK operation will only shrinks one empty LOB extent at a time. This is by design. And hence we may have to shrink multiple times to release the space to operating system. This behavior is because we do not deallocate the LOB pages; we save them for the next time we may need to insert LOB data. In certain cases, these allocated but empty LOB pages may accumulate. Again, this is by design.

Also When the LOB value to be inserted is larger than one page, we break it up into page-size fragments. And when we insert a page-size fragment, we don't search the existing LOB pages for free space; we instead just allocate a new page. This is an optimization: in the typical case, the free space fragments on the existing pages are all smaller than a page. Since we need a whole page of free space, we might as well skip the search and just allocate a new page.

NOTE: This behavior is by design and have been reported as a known issue.

Considering the above know behavior of SQL , which is by design the work around to release the empty LOB pages is as follows:

Use DBCC CLEANTABLE('databasename', 'tablename') to deallocate all the empty extents.

NOTE: The recommended option to delete all rows of a table is to use TRUNCATE option. Truncate has advantage over the Delete operation as I had mentioned in the previous email.

On your production database backup which I have restored at my end, when I ran the DBCC CLEANTABLE command. The sp_spaceused output after following the workaround was as follows:

Please run DBCC UPDATEUSAGE query on the table before you run SP_SPACEUSED query. Please refer link for more details: http://msdn.microsoft.com/en-us/library/ms188414(v=SQL.90).aspx

ADDITIONAL INFORMATION

•Pages and Extents

http://msdn.microsoft.com/en-us/library/ms190969(v=SQL.90).aspx

•Managing Extent Allocations and Free Space

http://msdn.microsoft.com/en-us/library/ms175195(v=SQL.90).aspx

•Managing Space Used by Objects

http://msdn.microsoft.com/en-us/library/ms187501(v=SQL.90).aspx

Juliet20120 SSC Journeyman Points: 97 More actions · Answer 12

Juliet20120

SSC Journeyman

Points: 97

October 22, 2010 at 9:02 am

#1240986

Did you try

Truncate table mytable

miksh Ten Centuries Points: 1344 More actions · Answer 13

Yujie Fu (10/22/2010)
Did you try
Truncate table mytable

Yes, it released a table space but in production we did DELELE and stuck with the issue.

Oksana March SSChasing Mays Points: 603 More actions · Answer 14

Miksh, I am trying to understand this better, why was dropping and recreating the table not a desirable solution that you left as a "last resort"? I ask because we drop and recreate tables all the time, it causes no problem.

Seraj Alam-256815 SSC Eights! Points: 804 More actions · Answer 15

Hi,

Did you run "sp_spaceused @updateusage= 'true'"? Hope it fixes.

Alternately, pl try below;

Create a filegroup and aleter table to move to the new file group.

Then again move the table to the original filegroup and remove the temporary file group you created.

This long process is because you do not intend to drop/create an empty table.

I have noticed such problem in SQL 2000 where ntext data type existed and it did not releases the space from table. But the scene was different there as the space were shown in "unused space" which is not the case here. I had to follow the second approach which worked for me.