How to free table space

Question

Post reply

How to free table space

Viewing 15 posts - 31 through 45 (of 65 total)

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Try deleting with (tablock) also, when you get a chance.

Or better yet, drop the indexes so the table is a heap, then delete with tablock, then recreate the clustered index.

miksh Ten Centuries Points: 1344 More actions · Answer 2

CirquedeSQLeil (9/27/2010)
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?

Yes, even dropped and created

miksh Ten Centuries Points: 1344 More actions · Answer 3

CirquedeSQLeil (9/27/2010)
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?

Nothing is pending, besides, I restored the prod db to stage as stand-alone db (where I do all testing) and I assume that no relication should be involved here, right?

Otherwise how to check it?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

check dbcc opentran

I wonder if there is something that is preventing the table from releasing space

Also, is the db in full or simple?

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: 281324 More actions · Answer 5

miksh (9/27/2010)
CirquedeSQLeil (9/27/2010)
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?
Nothing is pending, besides, I restored the prod db to stage as stand-alone db (where I do all testing) and I assume that no relication should be involved here, right?
Otherwise how to check it?

Check the publications under replication.

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 6

Derrick Smith (9/27/2010)
Try deleting with (tablock) also, when you get a chance.
Or better yet, drop the indexes so the table is a heap, then delete with tablock, then recreate the clustered index.

Just did the following: dropped all indexes, delete from table with tablock, created indexes - no affect.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Have you verified that the table is not a heap (has a clustered index)?

miksh Ten Centuries Points: 1344 More actions · Answer 8

CirquedeSQLeil (9/27/2010)
check dbcc opentran
I wonder if there is something that is preventing the table from releasing space
Also, is the db in full or simple?

No active open transactions (it was restored from prod backup on stage). DB in full.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

Michael Valentine Jones (9/27/2010)
Have you verified that the table is not a heap (has a clustered index)?

Very good point.

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 10

Michael Valentine Jones (9/27/2010)
Have you verified that the table is not a heap (has a clustered index)?

It has a cluster index but right now I removed ALL indexes, run 'delete from', truncate - still no effect. OK, will continue tomorrow. Thanks to everyone.

Perhaps, should prepare to drop the table. Maybe, create a ticket with Microsoft to understand what is wrong? 😛

Derrick Smith SSChampion Points: 10833 More actions · Answer 11

He/she did say it was a clustered PK back on page 2 or so..but just in case it wasn't, thats why I was suggesting deleting with a table lock. SQL won't release space on a heap unless you delete with a full table lock.

miksh Ten Centuries Points: 1344 More actions · Answer 12

Well, finally I gave up and dropped and created the table.

Before that I removed everything from that table (PK, FKs, indexes, constraints) and run delete, truncate but nothing has changed (maybe because no records in the table?).

After that I restored db on stage again and truncated the table (instead of deleting all records) and it did the trick and reset the table size to 0. Unfortunately, the records have been already deleted on production so I'll restore db again, delete records and try to find the solution.

Derrick Smith SSChampion Points: 10833 More actions · Answer 13

Thanks for keeping us updated. That's really odd behavior that it continues to show all of that space being used while it has a clustered index, 0 rows, no forwarded records, no replication or open transactions, or any of the other things that would normally cause that.

miksh Ten Centuries Points: 1344 More actions · Answer 14

Really weird. I removed everything in db except that table and everthing inside the table except columns (row count = 0) and the size issue is still over there. Neither DB shrink nor backup/restore helps.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 15

I find that extremely odd. Can we get the DDL for this table? Including the indexes?

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

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