September 27, 2010 at 2:43 pm
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.
September 27, 2010 at 2:47 pm
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
September 27, 2010 at 2:48 pm
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?
September 27, 2010 at 2:51 pm
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
September 27, 2010 at 2:53 pm
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
September 27, 2010 at 2:53 pm
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.
September 27, 2010 at 2:54 pm
Have you verified that the table is not a heap (has a clustered index)?
September 27, 2010 at 2:55 pm
CirquedeSQLeil (9/27/2010)
check dbcc opentranI 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.
September 27, 2010 at 2:57 pm
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
September 27, 2010 at 2:59 pm
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? 😛
September 27, 2010 at 3:00 pm
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.
September 28, 2010 at 8:42 am
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.
September 28, 2010 at 8:45 am
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.
September 28, 2010 at 11:55 am
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.
September 28, 2010 at 12:30 pm
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
Viewing 15 posts - 31 through 45 (of 65 total)
You must be logged in to reply to this topic. Login to reply