Reputation: 27043
I have a SQL 2005 database with one large table. I have ran a DELETE to prune some items from the table and I want to free the space back to the OS.
I have tried the following commands
DBCC SHRINKDATABASE (MyDB, TRUNCATEONLY);
DBCC SHRINKFILE (MyTable, TRUNCATEONLY);
and I have also rebuilt the clustered index with the following command
ALTER INDEX [IX_Clustered] ON [dbo].[MyTable] REBUILD WITH ( PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
The SHRINK commands had no effect and the index rebuild increased the database size by 35GB (the database is 1.5TB).
So, I'm at a loss. What do I need to do to regain unused database space?
Edit: The log file is not the problem. The main database .MDF file it too large.
Edit 2: Here is the results of sp_spaceused MyTable:
rows reserved data index_size unused
----------- ------------------ ------------------ ------------------ ------------------
1031649352 1543899648 KB 1481718624 KB 54444664 KB 7736360 KB
Upvotes: 2
Views: 454
Reputation: 711
did you delete LOB data, ie TEXT, NTEXT, IMAGE or XML datatypes?
sometimes you need to rebuild tables to reclaim the space if you have deleted LOB data.
Actually, having just looked at your figures - 99% of the space is taken up by data+index - where is the free space you are trying to reclaim?
Upvotes: 0
Reputation: 13932
Try re-indexing using DBREINDEX, updating the stats, then shrinking, I think that did the trick for me.
DBCC DBREINDEX ('?', ' ', 80)
GO
EXEC sp_updatestats
GO
Upvotes: 1
Reputation: 54725
Have you checked your log file size? Have you tried spaceused to see whether the table is still / indices are still occupying a lot of space?
Upvotes: 0