How to free table space

Question

How to free table space

Viewing 15 posts - 16 through 30 (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

miksh (9/27/2010)
unused[/b] space which is based on sp_spaceused 1864 KB while reserved space is 8441576 KB and data space is 8439672 KB

Just did it on stage and it worked as I expected, i.e. db shrink freed only soem of the space.

It was

namerowsreserved data index_sizeunused

mytbl0 6959728 KB6662424 KB0 KB297184 KB

and after shrinking

namerowsreserved dataindex_sizeunused

mytbl0 5526112 KB5318536 KB0 KB207576 KB

miksh Ten Centuries Points: 1344 More actions · Answer 2

Derrick Smith (9/27/2010)
Have you tried doing ALTER TABLE tablename REBUILD ?

ALTER TABLE tablename REBUILD did not change any table size returned by sp_spaceused

miksh Ten Centuries Points: 1344 More actions · Answer 3

Hmm... even TRUNCATE didn't work! Any suggestions before I re-created the table?

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Just for kicks, try this:

Delete from tablename ( with tablock )

see if it changes the data size, which should be 0.

edit: I doubt we'd find anything out of the ordinary, but can you post the output from the below please? :

DBCC SHOWCONTIG (tablename) WITH TABLERESULTS

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

raistlinx (9/27/2010)
getoffmyfoot (9/27/2010)
Yea, the drop/create table is brutal...
Maybe I am missing something here, could someone elaborate on why this is not good in this situation?

There used to be data in this table. The data was deleted. Data is inserted into this table on a regular basis. What downstream process will be affected by doing this drop create? That information would have to be gathered before going to an extreme option such as dropping and recreating the table. There is also the need to ensure that all FKs associated with this table are properly dropped and recreated. It's not just as simple as drop / create.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Run a dbcc checktable and determine if there is any corruption.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Is this table replicated?

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 8

Derrick Smith (9/27/2010)
Just for kicks, try this:
Delete from tablename ( with tablock )
see if it changes the data size, which should be 0.

Will do a little later after running DBCC CHECKDB

edit: I doubt we'd find anything out of the ordinary, but can you post the output from the below please? :
DBCC SHOWCONTIG (tablename) WITH TABLERESULTS

Not sure how to better post this:

ObjectNameObjectIdIndexNameIndexIdLevelPagesRowsMinimumRecordSizeMaximumRecordSizeAverageRecordSizeForwardedRecordsExtentsExtentSwitchesAverageFreeBytesAveragePageDensityScanDensityBestCountActualCountLogicalFragmentationExtentFragmentation

Mytbl2045250341PK_MyFK1000000000001000000

miksh Ten Centuries Points: 1344 More actions · Answer 9

CirquedeSQLeil (9/27/2010)
raistlinx (9/27/2010)
getoffmyfoot (9/27/2010)
Yea, the drop/create table is brutal...
Maybe I am missing something here, could someone elaborate on why this is not good in this situation?
There used to be data in this table. The data was deleted. Data is inserted into this table on a regular basis. What downstream process will be affected by doing this drop create? That information would have to be gathered before going to an extreme option such as dropping and recreating the table. There is also the need to ensure that all FKs associated with this table are properly dropped and recreated. It's not just as simple as drop / create.

Just trying to avoid several replications re-init.

miksh Ten Centuries Points: 1344 More actions · Answer 10

CirquedeSQLeil (9/27/2010)
Is this table replicated?

Yes but not on stage where I currently do testing.

miksh Ten Centuries Points: 1344 More actions · Answer 11

CirquedeSQLeil (9/27/2010)
Run a dbcc checktable and determine if there is any corruption.

It's when you don't like good news :crazy:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.

miksh Ten Centuries Points: 1344 More actions · Answer 12

Btw, one of the columns of XML type if it helps you.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

To recap:

sp_updatestats did not work

dbcc updateusage did not work

dbcc checkdb shows no corruption?

The table is replicated

Index Rebuild did not work

Truncate table did not work

Are there any pending replication actions?

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

miksh (9/27/2010)
CirquedeSQLeil (9/27/2010)
Run a dbcc checktable and determine if there is any corruption.
It's when you don't like good news :crazy:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.

I figured that might be the result.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

miksh (9/27/2010)
Btw, one of the columns of XML type if it helps you.

How many indexes?

Have all indexes been defragged / rebuilt?

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